Random timestamps between two intervals in Redshift

random_timestamps.sql

-- generates timestamps randomly between the start and end times
with
random_timestamps as (
    select
        -- some 'variables' just to make things clear
        '2021-01-01'::timestamp as time_start,
        sysdate as time_end,
        date_diff('minute', time_start, time_end) as time_span_minutes,

        -- pick a random number (0..1) and multiply by total number of minutes
        -- offset that from the start time to get a random time stamp in the chosen interval
        date_add('minute',(time_span_minutes * random())::integer, time_start) as ts
    from dw_demo.numbers n 
    where n.number < time_span_minutes / 2  -- one timestamp every two minutes
)

select
    date_trunc('day', ts) AS day,
    count(1) as total_timestamps
from random_timestamps
group by day
order by day asc