Looking for a way to add random dates to a column in one of your MySQL tables?
I ran across this problem and found a straightforward solution. By utilizing str_to_date(), concat(), floor() and rand() you can specify a random date for a column.
update mytable
set mycolumn = str_to_date(
concat(
floor(1 + rand() * (12-1)), /* Generate a random month */
'-',
floor(1 + rand() * (28 -1)), /* Generate a random day */
'-',
'2008'
),
'%m-%d-%Y'
);
Advertisement
Here is a better way to do that:
UPDATE tablename SET datefield= ’2005-01-01′ + interval rand()*883 day
where ’2005-01-01′ is the base date and 883 is the number days (maximum) you can go past the base date.
This will fill in each rows datefield with a random date between 2005-01-01 and 2006-06-02.
Hope that helps!
Thx Rick!
Here’s yet another way:
UPDATE tableName
SET column = FROM_UNIXTIME(RAND() * (UNIX_TIMESTAMP(’2015-7-20 15:00:00′) – UNIX_TIMESTAMP(’2008-09-10 12:00:00′)) + UNIX_TIMESTAMP(’2008-09-10 12:00:00′));
This will set the column’s value to a random datetime between ’2015-7-20 15:00:00′ and ’2008-09-10 12:00:00′.
If you only have a DATE field, you can extract the date from the end result. For example:
UPDATE tableName
SET column = DATE(FROM_UNIXTIME(RAND() * (UNIX_TIMESTAMP(’2015-7-20 15:00:00′) – UNIX_TIMESTAMP(’2008-09-10 12:00:00′)) + UNIX_TIMESTAMP(’2008-09-10 12:00:00′)));
The time part of the value is not required, so one could do UNIX_TIMESTAMP(’2025-01-05′) without worry.
Good luck!
Thanks!
Thanx Rick!
wow man, you saved my day