Mysql insert random datetime in a given datetime range

Question

With SQL , Can I insert random datetime values in a column giving a range?

For example, given a range of 2010-04-30 14:53:27 to 2012-04-30 14:53:27

I'm getting confused with the range part. as i will have just done this

INSERT INTO `sometable` VALUES (RND (DATETIME()))  

Answer 1

Here is an example that should help:

INSERT INTO `sometable` VALUES(     FROM_UNIXTIME(         UNIX_TIMESTAMP('2010-04-30 14:53:27') + FLOOR(0 + (RAND() * 63072000))     ) ) 

It uses the date 2010-04-30 14:53:27 as the base, converts that to a Unix timestamp, and adds a random number of seconds from 0 to +2 years to the base date and converts it back to a DATETIME.

It should be pretty close but over longer time periods leap years and other adjustments will throw it off.

Answer 2

This should work nicely:

SET @MIN = '2010-04-30 14:53:27'; SET @MAX = '2012-04-30 14:53:27'; SELECT TIMESTAMPADD(SECOND, FLOOR(RAND() * TIMESTAMPDIFF(SECOND, @MIN, @MAX)), @MIN); 

TIMESTAMPDIFF is used to determine the number of seconds in the date range. Multiply this by a random number between 0-1 results in a random number between 0 and the number of seconds in the range. Adding this random number of seconds to the lower bound of the range results in a random date between the data range bounds.