puRe aka Marcel Oelke

Random Timestamps in MySQL

14 Oct, 2007 MySQL

Have you ever needed a random timestamp in MySQL? For example to create demo data programmatically? Here’s my solution:

SELECT FROM_UNIXTIME(
  FLOOR(
    UNIX_TIMESTAMP('2007-01-01') +
        RAND() *
	(UNIX_TIMESTAMP('2007-01-03')-UNIX_TIMESTAMP('2007-01-01'))
    )
) as random_timestamp;

Or if you prefer a function:

CREATE FUNCTION random_timestamp (start TIMESTAMP, end TIMESTAMP)
RETURNS TIMESTAMP NOT DETERMINISTIC
RETURN FROM_UNIXTIME(
  FLOOR(
    UNIX_TIMESTAMP(start) +
    RAND() *
    (UNIX_TIMESTAMP(end)-UNIX_TIMESTAMP(start))
  )
);

mysql> select random_timestamp('2007-10-01', NOW());
+---------------------------------------+
| random_timestamp('2007-10-01', NOW()) |
+---------------------------------------+
| 2007-10-05 23:07:11                   |
+---------------------------------------+
1 row in set (0.00 sec)