CODEWITHSUNDEEP
mysql
37336792
37336792

Reputation: 493

Generate a rand time

I want to set random time value at created column, eg:

UPDATE review  set created=now()-rand(1,30)

But created=now()-rand(1,30) doesn't work. How to correct it?

Upvotes: 0

Views: 2422

Answers (1)

jordeu
jordeu

Reputation: 6821

You have to use DATE_ADD or DATE_SUB.

Something like this:

UPDATE
   review
SET
   created = DATE_ADD(NOW(), INTERVAL (24*RAND()) HOUR)

Check the reference to learn how to use date and time functions.

Here a more complex example that generates a random date starting 25 years ago.

UPDATE 
    review
SET
    created =
      DATE_ADD( DATE_ADD( DATE_ADD( DATE_ADD( DATE_ADD( DATE_ADD(
         NOW(), 
         INTERVAL (-24*RAND())-1 YEAR),
         INTERVAL 11*RAND() MONTH),
         INTERVAL 30*RAND() DAY),
         INTERVAL 23*RAND() HOUR),
         INTERVAL 59*RAND() MINUTE),
         INTERVAL 60*RAND() SECOND)

Upvotes: 6

Related Questions