MySQL Update multiple rows with unique timestamp per row

Question

For e.g. I have a table named people with the following columns-

people_id (primary key, auto inc)
first_name
last_name
timestamp (default - current_timestamp)

This table already had 30,000 rows before the last column timestamp was added, so at present all the timestamps are the same. I want each timestamp to be unique, but I am unable to do so.

Closest answer on stackoverflow I could found, is of Oracle, which suggests the following query -

UPDATE ITEM_HISTORY SET my_timestamp = SYSTIMESTAMP + NUMTODSINTERVAL(rownum/1000, 'SECOND');

But this query does not work in MySQL, because there are no such functions for NUMTODSINTERVAL and SYSTIMESTAMP. I tried to use above mentioned query as -

UPDATE people SET `timestamp` = CURRENT_TIMESTAMP + people_id;

But it failed, I think because, people_id (int) can't be added to timestamp. So is there any way to convert and add the people_id in timestamp or any other way to update rows with unique timestamps?

Thanks.

UPDATE people SET `timestamp` = CURRENT_TIMESTAMP + INTERVAL people_id MINUTE; — Pardeep Dhingra
– Pardeep Dhingra, Commented Dec 29, 2015 at 13:44
Requiring timestamps to be unique seems like a very bad criterion, and one that is bound to cause significant headaches further down the line. Look again at your requirements. — eggyal
– eggyal, Commented Dec 29, 2015 at 14:06
@eggyal Yes, I read the same about it on the linked SO answer. But, I just just need them to be unique to push the newly updated rows to android app (and for the first run of pushing this update was causing problem, because all 30K rows had same timestamps), I was earlier going with Unique/Primary Id only, but then I couldn't push latest updated rows. Can you suggest any alternate for this? Thanks, — Dr. Atul Tiwari
– Dr. Atul Tiwari, Commented Dec 29, 2015 at 14:10
You haven't provided any explanation of why the push didn't work when the timestamps were all the same—something that will be a property of your application, about which I know nothing. Perhaps ask a question about that instead? — eggyal
– eggyal, Commented Dec 29, 2015 at 14:14
@eggyal Your question made me think of my requirements again. I will go through it, without using timestamps, and will ask separate question, if needed any help. Thanks for your mention. — Dr. Atul Tiwari
– Dr. Atul Tiwari, Commented Dec 29, 2015 at 14:21

m.antkowicz · Accepted Answer · 2015-12-29 13:46:45Z

2

You can add some second using interval based on your user's id

    UPDATE people SET `timestamp` = DATE_ADD(CURRENT_TIMESTAMP , INTERVAL `id` SECOND);

Read more here about DATE_ADD() function

answered Dec 29, 2015 at 13:46

m.antkowicz

13.7k22 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dr. Atul Tiwari Over a year ago

It worked. Thanks for both answer and link of the function. I will accept the answer as soon as stackoverflow timelimit allows me.

Viktor · Accepted Answer · 2015-12-29 13:47:54Z

0

It works for me UPDATE people SET timestamp = UNIX_TIMESTAMP() + people_id;

answered Dec 29, 2015 at 13:47

Viktor

511 silver badge4 bronze badges

Comments

Ash · Accepted Answer · 2015-12-29 13:50:10Z

0

But it failed, I think because, people_id (int) can't be added to timestamp

Sure it can. you could use UNIX_TIMESTAMP

UPDATE people SET timestamp = DATE_ADD(UNIX_TIMESTAMP(), INTERVAL id SECOND);

edited Dec 29, 2015 at 13:50

answered Dec 29, 2015 at 13:43

Ash

3,4792 gold badges27 silver badges36 bronze badges

Collectives™ on Stack Overflow

MySQL Update multiple rows with unique timestamp per row

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related