随机日期/时间(Random date/time)
Need to generate a random date in MySQL? Here:
需要在MySQL中生成一个随机日期吗? 这里:
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)
好,为什么2147483647? (OK, why 2147483647?)
Well, 2147483647 is the latest timestamp:
好吧,2147483647是最新的时间戳:
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:
如果再尝试一秒钟(214748364 8 ),则太多了:
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.
因此RAND()
为您提供0到1之间的随机值,然后将其与可能的最大值相乘,这样您就可以得到1969年到2038年之间的随机日期。
What if you want to generate random date in a defined period?
如果要在定义的期间内生成随机日期该怎么办?
2009年的随机日期 (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:
如果您想将值限制在某个范围内,例如在start
和end
之间,并且您有一个介于0和1之间的随机值,则需要减去end - start
,它会为您提供所需的间隔,然后将其“移动”到开始(范围的开始)。 因此,超级duper复杂公式如下:
(end - start) * RAND + start
(end - start) * RAND + start
And because you cannot possibly remember timestamps, UNIX_TIMESTAMP comes to the rescue:
而且由于您可能不记得时间戳了,所以UNIX_TIMESTAMP可以解决:
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
在Facebook和Twitter上告诉您的朋友有关此帖子的信息