samedi 22 septembre 2018

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.

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 with sorting (and it will need to be computed for every row).

Sample data / Explanation

Given time constraints of:

  • start_timestamp => 2016-01-01 10:00:00
  • end_timestamp => 2017-12-31 18:00:00

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.




Aucun commentaire:

Enregistrer un commentaire