October 6th, 2010 by jde

I have a database with a table containing user accounts. Since it is a database with test-data for development, I am the only one using it, but I need it to look like all those users have been logged in recently. Therefore I need to populate the date field with random dates.

First create a table for testing:

USE test;

CREATE TABLE random_dates (
date_id int(6) not null auto_increment,
random_date datetime not null,
primary key (date_id)
);

Insert dates within a range of the last 90 days:

INSERT INTO random_dates (random_date) values
(DATE_SUB(NOW(),INTERVAL RAND()*7776000 SECOND)),
(DATE_SUB(NOW(),INTERVAL RAND()*7776000 SECOND)),
(DATE_SUB(NOW(),INTERVAL RAND()*7776000 SECOND)),
(DATE_SUB(NOW(),INTERVAL RAND()*7776000 SECOND)),
(DATE_SUB(NOW(),INTERVAL RAND()*7776000 SECOND)),
(DATE_SUB(NOW(),INTERVAL RAND()*7776000 SECOND));

SELECT * FROM random_dates;
+---------+---------------------+
| date_id | random_date         |
+---------+---------------------+
|       1 | 2010-09-10 09:10:27 |
|       2 | 2010-08-15 07:15:25 |
|       3 | 2010-10-02 23:33:53 |
|       4 | 2010-08-25 14:51:22 |
|       5 | 2010-09-16 18:23:21 |
|       6 | 2010-08-03 14:10:49 |
+---------+---------------------+

You could also insert dates within a range of the next 90 days:

INSERT INTO random_dates (random_date) values(DATE_ADD(NOW(),INTERVAL RAND()*7776000 SECOND));

Updating is just as easy:

UPDATE random_dates SET random_date = DATE_ADD(NOW(),INTERVAL RAND()*7776000 SECOND);

I use SECOND because the column I am updating is of the DATETIME datatype, and I also want the time part to be random. The following example could be used, if your field is just a DATE field (date only, no time):

UPDATE random_dates SET random_date = DATE_ADD(CURDATE(),INTERVAL RAND()*90 DAY);

Happy randomizing