Random MySQL date

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!

Tell your friends about this post on Facebook and Twitter