Mysql insert random datetime in a given datetime range

sql

randomness

datetime

performance

byAlex Kataev·Jan 8, 2025

Insert a random datetime within a preset range using TIMESTAMPADD and RAND(). Generate a random number of seconds and add it to the start date. Here’s a concise query to insert a random datetime between two dates in a table:

INSERT INTO table_name (datetime_column) VALUES (TIMESTAMPADD(SECOND, FLOOR(RAND() * TIMESTAMPDIFF(SECOND, '2021-01-01', '2021-12-31')), '2021-01-01'));

Remember to plug in your table_name, datetime_column, and your required date range.

Achieving true randomness

Ensure that each moment within the range has an equal probability of being selected. Use FROM_UNIXTIME and UNIX_TIMESTAMP 👈 it's not a band name, it's to convert datetimes to and from Unix epoch (seconds since 1970), thus avoiding any bias:

INSERT INTO table_name (datetime_column) 
VALUES (FROM_UNIXTIME(
  UNIX_TIMESTAMP('2021-01-01') + 
  FLOOR(RAND() * (UNIX_TIMESTAMP('2021-12-31') - UNIX_TIMESTAMP('2021-01-01') + 1))
));

Cross-check and validate if these random dates are leap year proof and adjust the range to your needs.

Constraints within anomalies

Keep your random dates within a specific business-relevant range, by adding an extra layer of validation:

SET @startDate = '2021-01-01 00:00:00', @endDate = '2021-12-31 23:59:59';
INSERT INTO table_name (datetime_column)
SELECT * FROM (
  SELECT TIMESTAMPADD(SECOND, FLOOR(RAND() * TIMESTAMPDIFF(SECOND, @startDate, @endDate)), @startDate) AS random_date
) AS subquery
WHERE random_date BETWEEN @startDate AND @endDate;

This subquery with a WHERE clause 🕶️ handles any datetimes that may fall outside the range due to rounding or other anomalies.

Adopting distribution strategies

When you need to control the standard deviation or when the distribution of random datetimes has to match a pattern (say, more Mondays), tweak how you generate randomness:

INSERT INTO table_name (datetime_column)
VALUES (
  TIMESTAMPADD(SECOND, 
    FLOOR(
        RAND() * 1 +          -- Adding just a pinch of randomness 🔥
        CASE DAYOFWEEK(CURDATE()) 
          WHEN 2 THEN 100      -- Make Mondays Great Again ✌️
          ELSE 0
        END
    ), 
  @startDate)
);

Customise the case statement to orient your randomness towards desired dates or times.

Optimizing performance leaps

Got large datasets or need random datetime inserts—like a lot? Performance takes a stride. Use variable assignments to lighten the compute during the insert operation:

SET @diff = TIMESTAMPDIFF(SECOND, @startDate, @endDate);
SET @randomSeconds = FLOOR(RAND() * (@diff + 1));

INSERT INTO table_name (datetime_column)
VALUES (TIMESTAMPADD(SECOND, @randomSeconds, @startDate));