[Kimberling City]

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!