CODEWITHSUNDEEP
mysqlsql
Green
Green

Reputation: 30805

How to update timestamp column so that every row in a table would have a random value?

What I want to achieve is a random timestamp value for every row in a table but beginning from some predefined time, like in this pseudo code:

UPDATE `table` SET `time_column` = RANDOM_TIMESTAMP() NOT LESS THAN NOW() - 1 HOUR;

Currently I do this and I get the same timestamp value for all rows. 

UPDATE `table` SET `time` = NOW();

id | time_column
--------------
1  | 100
2  | 100
3  | 100

Is this operation possible with MySQL only? In one UPDATE query?

Upvotes: 0

Views: 222

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can do something like this:

set @n := 0;
update `table`
    set time = UNIX_TIMESTAMP(NOW() - INTERVAL 1 HOUR + INTERVAL (@n := @n + 1) SECOND);

Initial approach by Gordon Linoff

set @n := 0;
update `table`
    set time = (now() - interval 1 hour) + interval (@n := @n + 1) second;

Initial approach is also important to know.

Upvotes: 2

Related Questions