MYSQL Subctract Days and Seconds

Greeting all

I 开发者_Python百科update my field 'createdOn' datetime field to hold values of last 7 days in random like this:

UPDATE posts
   SET createdOn
     = DATE_SUB(DATE(NOW()), INTERVAL ROUND(RAND()*7) DAY)

Although this gives me the random dates I need, the hours, minutes and seconds are not random and are like 00:00:00. How can I include random hours, minutes and seconds also in the above? Unique hours, minutes and seconds in the last seven days would be even better.

Thanking you

Here it is an example of random hours, minites and seconds in the last seven days -

UPDATE posts
  SET createdOn = DATE(NOW()) - INTERVAL FLOOR(RAND() * 604800) SECOND;

Where 604800 = 60 seconds * 60 minutes * 24 hours * 7 days.

You're casting NOW() as a DATE, which will strip out the time portion, and force it to 00:00:00. Perhaps do another RAND() on TIME(NOW()) and adding them together.