CODEWITHSUNDEEP
sqldatabasepostgresqldatetimetimestamp
Matthiew
Matthiew

Reputation: 647

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

The title is pretty much explicit, my question is if i get two dates with hour:

Is it possible to pick a random datetime between these two datetime ?

I tried with the random() function but i don't really get how to use it with datetime

Thanks

Matthiew

Upvotes: 62

Views: 55036

Answers (4)

Big_Boulard
Big_Boulard

Reputation: 1335

The formula is SELECT random() * (b - a) + a; where a is the smallest number and b is the largest number that you want to generate a random number for.

SELECT random() * (timestamp '2023-09-01 20:00:00' - timestamp '2023-09-01 10:00:00') + timestamp '2023-09-01 10:00:00';

or

SELECT random() * ('2023-09-01 20:00:00'::timestamp - '2023-09-01 10:00:00'::timestamp) + '2023-09-01 10:00:00'::timestamp;

Upvotes: 1

pozs
pozs

Reputation: 36244

You can do almost everything with the date/time operators:

select timestamp '2014-01-10 20:00:00' +
       random() * (timestamp '2014-01-20 20:00:00' -
                   timestamp '2014-01-10 10:00:00')

Upvotes: 122

Dustin
Dustin

Reputation: 1026

I adapted @pozs answer, since I didn't have timestamps to go off of.

90 days is the time window you want and the 30 days is how far out to push the time window. This is helpful when running it via a job instead of at a set time.

select NOW() + (random() * (NOW()+'90 days' - NOW())) + '30 days';

Upvotes: 35

Denis de Bernardy
Denis de Bernardy

Reputation: 78513

You could build the timestamp from a random integer (unix stamp), e.g.:

select timestamp 'epoch' + (
          extract('epoch' from timestamp '2014-10-01 10:00:00')
        + random() * (
                     extract('epoch' from timestamp '2014-20-01 20:00:00')
                   - extract('epoch' from timestamp '2014-10-01 10:00:00')
       )) * interval '1 second'

Wrap it in an SQL function if you use it often, as it's not very readable even after attempting to format it somewhat.

Another way to do it would be to use generate_series() from the start date to the end date, ordered by random(), but that would make things very slow with larger date intervals so you'll be better off with the above approach.

Upvotes: 7

Related Questions