With SQL , Can I insert random datetime values in a column giving a range?
For example, given a range of 2010-04-30 14:53:27 to 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()))
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:27 as 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.
It should be pretty close but over longer time periods leap years and other adjustments will throw it off.
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);
TIMESTAMPDIFF is 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.
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 datetime using from_unixtime. This way you can ensure that each option has equal probability.
Easiest way out:
INSERT INTO `sometable` VALUES (SELECT timestamp('2010-04-30 14:53:27') - INTERVAL FLOOR( RAND( ) * 366) DAY);
Just try :
SELECT TIMESTAMP('2012-04-30 14:53:27')-INTERVAL RAND()*365*2 DAY INTO tbl_name;
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
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;
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.
Here is my code :
SELECT DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP('2015-01-01') + FLOOR(0 + (RAND() * 63072000))), '%Y-%m-%d');
Use following dynamic query.
SET @MIN = NOW() - INTERVAL 2 MONTH;
SET @MAX = now();
select TIMESTAMPADD(SECOND, FLOOR(RAND() * TIMESTAMPDIFF(SECOND, @MIN, @MAX)), @MIN)
Thanks to @sapna-bhayal
