Add Random Dates to MySQL Database

[Kersey]

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'
                                );

[Keweenaw]

Here is a better way to do that:

UPDATE tablename SET datefield= ‘2005-01-01′ + interval rand()*883

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!

[Kiefer]

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!