Random date/time
Need to generate a random date in MySQL? Here:
mysql> SELECT FROM_UNIXTIME(RAND() * 2147483647) AS `rand`; +---------------------+ | rand | +---------------------+ | 1998-04-01 21:42:48 | +---------------------+ 1 row in set (0.00 sec)
Let's try once again and get a different random value:
mysql> SELECT FROM_UNIXTIME(RAND() * 2147483647) AS `rand`; +---------------------+ | rand | +---------------------+ | 2028-03-21 22:44:43 | +---------------------+ 1 row in set (0.00 sec)
OK, why 2147483647?
Well, 2147483647 is the latest timestamp:
mysql> SELECT FROM_UNIXTIME(2147483647) AS `rand`; +---------------------+ | rand | +---------------------+ | 2038-01-18 19:14:07 | +---------------------+ 1 row in set (0.00 sec)
If you try one more second (2147483648), that's too much:
mysql> SELECT FROM_UNIXTIME(2147483648) AS `rand`; +------+ | rand | +------+ | NULL | +------+ 1 row in set (0.01 sec)
So RAND()
gives you a random value between 0 and 1 and you multiply it with the largest value posible and this way you get a random date between 1969 and 2038.
What if you want to generate random date in a defined period?
A random date in 2009
Here goes:
mysql> SELECT FROM_UNIXTIME(RAND() * (1262246400 - 1230796800) + 1230796800) AS `the_date`; +---------------------+ | the_date | +---------------------+ | 2009-04-27 05:01:04 | +---------------------+ 1 row in set (0.00 sec)
And once again:
mysql> SELECT FROM_UNIXTIME(RAND() * (1262246400 - 1230796800) + 1230796800) AS `the_date`; +---------------------+ | the_date | +---------------------+ | 2009-08-15 12:54:51 | +---------------------+ 1 row in set (0.01 sec)
"What just happened?"
When you want to limit the values in a range, say between start
and end
and you have a random value between 0 and 1 you'd need to subtract end - start
which gives you the desired interval and then "move" the interval to the start (the beginning of the range). So the super-duper complicated formula is like:
(end - start) * RAND + start
And because you cannot possibly remember timestamps, UNIX_TIMESTAMP comes to the rescue:
mysql> SELECT -> UNIX_TIMESTAMP('2009-01-01') AS start, -> UNIX_TIMESTAMP('2009-12-31') AS end; +------------+------------+ | start | end | +------------+------------+ | 1230796800 | 1262246400 | +------------+------------+ 1 row in set (0.00 sec)
Thanks
Thanks for reading!