Postgresql Get a random datetime/timestamp between two datetime/timestamp

sql

performance

best-practices

timestamp

byNikita Barsukov·Nov 22, 2024

To generate a random timestamp within a range, use RANDOM() and interval arithmetic. The winning formula, START + (END - START) * RANDOM(), lets you land anywhere in the interval.

SQL snippet with a hint of fun:

SELECT
  '2023-01-01 00:00:00'::timestamp + -- Our story begins here
  ( '2023-12-31 23:59:59'::timestamp - '2023-01-01 00:00:00'::timestamp ) * -- This is the timeline
  RANDOM() AS random_timestamp; -- Make it...random!

This gives you a random timestamp 'somewhere between' January 1st and December 31st, 2023.

Digging into the logic

The magic lies in how RANDOM() operates. It returns a pseudo-random number between 0 and 1. When you multiply this fraction by the time interval and add it to the start datetime, you're picked up and magically dropped at some random point in your time range.

Proceed with caution! Make sure the start and end values are already in timestamp format for smooth sailing. If you're starting with strings, you need this cast:

'YYYY-MM-DD HH:MI:SS'::timestamp

Let's talk efficiency. If you're dealing with multiple random datetime generations, consider putting this logic into a neat little SQL function.

And performance! You might be tempted to use generate_series() for large intervals, but be warned, it can lead to slow performances. Stick to the shown calculation for light speed results.

Tailoring to your time window

Our method is a flexible tool. Replace the start and end datetimes within the SQL snippet with your own, and you're ready to generate random timestamps at will:

Specific hours? No problem:

SELECT
  '2023-01-01 08:00:00'::timestamp + -- Early bird 
  ( '2023-01-01 17:00:00'::timestamp - '2023-01-01 08:00:00'::timestamp ) * -- Shift's over
  RANDOM() AS random_work_hour_timestamp;  -- Randomness to spice up the day

Range spanning multiple days? We got you:

SELECT
  '2023-06-01 00:00:00'::timestamp + -- From this day forward
  ( '2023-06-07 23:59:59'::timestamp - '2023-06-01 00:00:00'::timestamp ) * -- Till seven days later
  RANDOM() AS random_full_week_timestamp;  -- Each day brings a surprise timestamp

So do a bit of customizing and, as always, test your queries with different ranges to ensure accuracy.

Structure: Keep your query upright

Follow these steps to guarantee perfect SQL structure and avoid any syntax quicksand:

Begin with a SELECT
Do your calculations within the SELECT statement
Embrace your parentheses to ensure correct order of operations
End with an alias so everyone knows what you're talking about

Taking care of business (hours)

Got some specific times to respect or exclude? Add a bit of conditional logic to the mix like a SQL pro:

SELECT
  ts
FROM (
  SELECT 
    '2023-01-01'::date + (RANDOM() *
    ('2023-12-31'::date - '2023-01-01'::date + 1)) AS ts -- Generate a random date in 2023
) AS r
WHERE 
  EXTRACT('ISODOW' FROM ts) < 6 AND -- Sorry, we're closed on weekends
  ts::time BETWEEN '09:00' AND '17:00'; -- And we work 9-5, just like Dolly Parton

This snippet is your ticket to a business hours timestamp from Monday-Friday.

Power tips for interval generation

Look to EPOCH conversion for precise interval calculations.
When time zones enter the scene, be sure to accommodate for these in your datetime calculations.
Good range practice: Run a validation check before generating. Accidents happen.
If you're filtering on timestamp columns, indexing might just become your best friend, significantly improving performance.