CODEWITHSUNDEEP
randompostgresql-10sql-timestamp
Kamil Gosciminski
Kamil Gosciminski

Reputation: 17177

PostgreSQL Get random value between two timestamps with hours constraint

Using: PostgreSQL 10.5

This question is somewhat similar to:

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

The answer given by @pozs solves the problem but doesn't let me constraint the hours within random timestamp returned. I'd say this is an extention of this issue.

Task

The difference is that I need to get a random timestamp between two timestamps, but the hour in the output value has to be between 10:00:00 and 18:00:00.

My attempt

I've been trying to do it time efficiently, but for now have only come up with the idea of storing different parts of: date, time and miliseconds and then combining them with 3 selects using ORDER BY random() LIMIT 1. This is, however, far from a fast solution.

tmp_data holds dates, tmp_time holds time and tmp_ms holds miliseconds, which I add together using a function to get proper output:

  (SELECT data FROM tmp_data ORDER BY random() LIMIT 1) 
+ (SELECT czas FROM tmp_time WHERE czas BETWEEN '10:00:00' AND '18:00:00' ORDER BY random() LIMIT 1) 
+ (SELECT ms FROM tmp_ms ORDER BY random() LIMIT 1)

This get's the job done, but requires some time due to 3 selects to precomputed tables with sorting (and it will need to be computed for every row).

Sample data / Explanation

Given time constraints of:

Let's generate random timestamp in terms of every part but hour (hour has to be between 10 and 18).

Sample output - randomly generated

 2016-09-12 11:54:59.4919
 2016-01-10 10:39:03.626985
 2016-01-03 15:58:19.599016
 2016-04-11 10:05:07.527829
 2016-07-04 12:57:33.125333
 2017-12-15 14:17:46.975731
 2016-10-04 16:55:01.701048
 2016-09-26 13:36:59.71145
 2017-09-06 17:25:09.426963
 2016-09-08 17:08:00.917743

Each hour here is between 10 and 18, but every other part of the timestamp is random.

Upvotes: 0

Views: 2557

Answers (2)

S-Man
S-Man

Reputation: 23716

If you convert the timestamps into seconds with EXTRACT(epoch FROM <YOUR TIMESTAMP>) you can do this:

  1. random() gives a value between 0 and 1
  2. if you multiply the random value with the timestamps difference you get the possible range (for 10 to 18 hours the range would be between 0 and 8 hours)
  3. add a starting point to the range shifts the calculated value to a value between you starting and you end point. Now you have your expected random timestamp in seconds
  4. convert the seconds back to the Postgres timestamp with to_timestamp
  5. do this 2 times: One time to get the random date and one time to get the random time.
  6. add both timestamps (one casted into date, one into time).

The query

WITH timestamps AS (

    SELECT 
        EXTRACT(epoch FROM start::time) start_time,
        EXTRACT(epoch FROM "end"::time) end_time,
        EXTRACT(epoch FROM start::date) start_date,
        EXTRACT(epoch FROM "end"::date) end_date
    FROM (
        SELECT
            '2016-01-01 10:00:00'::timestamp as start,
            '2017-12-31 18:00:00'::timestamp as end
    ) s
)

SELECT 
    to_timestamp(
        random() * (ts.end_date - ts.start_date) + ts.start_date 
    )::date + 
    to_timestamp(
        random() * (ts.end_time - ts.start_time) + ts.start_time 
    )::time 
FROM timestamps ts

demo:db<>fiddle

Upvotes: 0

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17177

Converting the output from this answer to date type, then adding 10:00:00 time (lower hour constraint) and random interval of up to 8 hours (upper hour constraint) does this pretty quickly:

select 
  date (timestamp '2016-01-01' + 
        random() * (timestamp '2017-12-31' - timestamp '2016-01-01'))
 + time '10:00:00' 
 + random() * INTERVAL '8 hours';

Upvotes: 3

Related Questions