随机MySQL日期

随机日期/时间(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上告诉您的朋友有关此帖子的信息

翻译自: https://www.phpied.com/random-mysql-date/