Skip to content
Advertisement

Limit the random value of timestamp from january to yesterday date

How can i limit the randomize timestamp on my query from 2020-01-01 up to this date or the date of yesterday.

This is my current code that i’m experimenting and trying to use and play around but it keeps inserting data with a year 2019..

UPDATE dashboard.event
    SET event_start_adj = '2020-04-01  00:00:00'::timestamp + 
                          date_trunc('second',
                                     (random() * ('2020-01-01 00:00:00'::timestamp - '2020-04-16 23:59:59'::timestamp))
                                     )

Advertisement

Answer

This expression will produce a random date/time between Jan 1 and the current time:

select '2020-01-01'::date + random() * (now() - '2020-01-01'::date)

You can replace now() with a constant, if you like.

In an update, this would be:

UPDATE dashboard.event
    SET event_start_adj = '2020-01-01'::date + random() * (now() - '2020-01-01'::date)
User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement