Quick Answer: 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.

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

8 Responses to Quick Answer: Add Random Dates to MySQL Database

  1. Rick says:

    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!

  2. Sandro says:

    Thx Rick! ๐Ÿ˜‰

  3. Jed says:

    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!

  4. Alex says:

    Thanx Rick! ๐Ÿ™‚

  5. metal says:

    wow man, you saved my day

  6. Gabriel says:

    1. I also added a possibility to generate a random year, for an adult (18+), in a 70 years range from the current date.

    SELECT STR_TO_DATE(
    concat(
    floor(1 + rand() * (12 – 1)), /* Generate a random month */
    ‘-‘,
    floor(1 + rand() * (28 – 1)), /* Generate a random day */
    ‘-‘,
    YEAR(CURDATE() – INTERVAL (floor(18 +(rand() * 70))) YEAR) /* Generate a random year, for an adult (18+) in an interval ‘X’ years back from current date */
    ),
    ‘%m-%d-%Y’
    ) AS Generated_data;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: