ylliX - Online Advertising Network

Quick tip – how to generate MySQL random date/time in given range?

Just use:

SELECT DATE_FORMAT(
    FROM_UNIXTIME(
         RAND() * 
            (UNIX_TIMESTAMP('2012-11-13 16:00:00') - UNIX_TIMESTAMP('2012-11-13 23:00:00')) + 
             UNIX_TIMESTAMP('2012-11-13 23:00:00')
                  ), '%Y-%m-%d %H:%i:%s')

where first param UNIX_TIMESTAMP(‘2012-11-13 16:00:00’) is start value, second UNIX_TIMESTAMP(‘2012-11-13 23:00:00’) is end value, it has to be repeated twice like shown here:

SELECT DATE_FORMAT(
  FROM_UNIXTIME(RAND() * (starttime- endtime) + endtime)
  , '%Y-%m-%d %H:%i:%s')

Leave a Reply