Mysql 在给定的日期时间范围内插入随机日期时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11906572/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Mysql insert random datetime in a given datetime range
提问by karto
With SQL , Can I insert random datetime values in a column giving a range?
使用 SQL ，我可以在给出范围的列中插入随机日期时间值吗？
For example, given a range of 2010-04-30 14:53:27to 2012-04-30 14:53:27
例如，给定范围2010-04-30 14:53:27为2012-04-30 14:53:27
I'm getting confused with the range part. as i will have just done this
我对范围部分感到困惑。因为我刚刚做了这个
INSERT INTO `sometable` VALUES (RND (DATETIME()))
回答by drew010
Here is an example that should help:
这是一个应该有帮助的例子：
INSERT INTO `sometable` VALUES(
FROM_UNIXTIME(
UNIX_TIMESTAMP('2010-04-30 14:53:27') + FLOOR(0 + (RAND() * 63072000))
)
)
It uses the date 2010-04-30 14:53:27as the base, converts that to a Unix timestamp, and adds a random number of seconds from 0 to +2 years to the base date and converts it back to a DATETIME.
它使用日期2010-04-30 14:53:27作为基准，将其转换为 Unix 时间戳，并将从 0 到 +2 年的随机秒数添加到基准日期并将其转换回 DATETIME。
It should be pretty close but over longer time periods leap years and other adjustments will throw it off.
它应该非常接近，但在更长的时间段内闰年和其他调整会使其失效。
回答by Brent Worden
This should work nicely:
这应该很好用：
SET @MIN = '2010-04-30 14:53:27';
SET @MAX = '2012-04-30 14:53:27';
SELECT TIMESTAMPADD(SECOND, FLOOR(RAND() * TIMESTAMPDIFF(SECOND, @MIN, @MAX)), @MIN);
TIMESTAMPDIFFis used to determine the number of seconds in the date range. Multiply this by a random number between 0-1 results in a random number between 0 and the number of seconds in the range. Adding this random number of seconds to the lower bound of the range results in a random date between the data range bounds.
TIMESTAMPDIFF用于确定日期范围内的秒数。将此乘以 0-1 之间的随机数会得到 0 和范围内秒数之间的随机数。将此随机秒数添加到范围的下限会导致数据范围范围之间的随机日期。
回答by Pacerier
This works perfectly even for leap years:
即使在闰年，这也能完美运行：
select from_unixtime(
unix_timestamp('2000-1-1') + floor(
rand() * (
unix_timestamp('2010-12-31') - unix_timestamp('2000-1-1') + 1
)
)
)
The idea is simple: Just take a random timestamp between the two timestamps, then convert it to a datetimeusing from_unixtime. This way you can ensure that each option has equal probability.
这个想法很简单：只需在两个时间戳之间取一个随机时间戳，然后将其转换为datetimeusing from_unixtime。这样您就可以确保每个选项的概率相等。
回答by Pankaj Sharma
Easiest way out:
最简单的出路：
INSERT INTO `sometable` VALUES (SELECT timestamp('2010-04-30 14:53:27') - INTERVAL FLOOR( RAND( ) * 366) DAY);
回答by NooNa MarJa
Just try :
你试一试 ：
SELECT TIMESTAMP('2012-04-30 14:53:27')-INTERVAL RAND()*365*2 DAY INTO tbl_name;
回答by PrJ
This worked for me but my issue was a bit different. I had to assign certain values in a column to a random datetime.
这对我有用，但我的问题有点不同。我必须将列中的某些值分配给随机日期时间。
UPDATE Tablename
SET columnName = addtime(concat_ws(' ','2018-07-25' + interval rand()*2 day
,'00:00:00'),sec_to_time(floor(0 + (rand() * 86401))))
WHERE columnName = condition;
回答by Sapna Bhayal
SET @MIN = '2019-06-29 00:53:27';
SET @MAX = '2019-06-29 13:53:27';
UPDATE tablename
SET columnname = TIMESTAMPADD(SECOND, FLOOR(RAND() * TIMESTAMPDIFF(SECOND, @MIN, @MAX)), @MIN)
WHERE `columnname` = condition
回答by Lukenzo
It's an old thread but still.. In my case I needed to generate random date in format like this : 2017-01-01. If anyone will need it I have used @drew010 solution and formatted date with DATE_FORMAT.
这是一个旧线程，但仍然......在我的情况下，我需要以如下格式生成随机日期：2017-01-01。如果有人需要它，我已经使用了@drew010 解决方案并使用 DATE_FORMAT 格式化了日期。
Here is my code :
这是我的代码：
SELECT DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP('2015-01-01') + FLOOR(0 + (RAND() * 63072000))), '%Y-%m-%d');
