CODEWITHSUNDEEP
mysqlsql
ShadowAccount
ShadowAccount

Reputation: 321

Set MySQL timestamp to random time within past 24 hours

I have a timestamp column in my MySQL table.

I'm wanting to set this timestamp to a random time within the past 24 hours for all rows in the table.

I know I can update all the rows doing this:

UPDATE table SET timestamp =

But I can't find if there's a way to set a random timestamp that's occurred within the past 24 hours so that each row has a different time.

Upvotes: 0

Views: 792

Answers (3)

Pankaj_Dwivedi
Pankaj_Dwivedi

Reputation: 580

You can try: 

Update table set timestamp = select(cast((sysdate() - floor(rand()*24)) AS Datetime));

Upvotes: 0

nbk
nbk

Reputation: 49403

You can use Unixtimestqamps for that

UPDATE table1
    SET timestamp = (SELECT TIMESTAMPADD(SECOND, 
          FLOOR(RAND() * TIMESTAMPDIFF(SECOND, NOW() - INTERVAL 1 DAY, NOW()))
          , NOW() - INTERVAL 1 DAY));

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270773

You can use:

UPDATE table
    SET timestamp = now() - interval floor((24*60*60)*rand()) second;

Upvotes: 0

Related Questions