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!