phpied.com

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!

This entry was posted on Thursday, January 29th, 2009 and is filed under mysql. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Get notification for future posts: follow me on Twitter or subscribe to my RSS feed