网络营销电子商务研究中心 - Add Random Dates to MySQL Database

网络营销电子商务研究中心 (https://www.0058.net/index.php)

- MySQL (https://www.0058.net/forumdisplay.php?f=76)

- - Add Random Dates to MySQL Database (https://www.0058.net/showthread.php?t=5091)

Add Random Dates to MySQL Database

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.

Code:

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'

                                );

Here is a better way to do that:

Code:

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!

Here’s yet another way:

Code:

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:

Code:

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!