CODEWITHSUNDEEP
postgresql
Darkisa
Darkisa

Reputation: 2037

Postgres tsrange, filter by date and time

I have an events table that has a field called duration thats of type tsrange and that captures the beginning and end time of an event thats of type timestamp. What I want is to be able to filter all events across a certain date range and then filter those events by time. So for instance, a user should be able to filter for all events happening between (inclusive) 12-15-2019 to 12-17-2019 and that are playing at 9PM. To do this, the user submits a date range which filters all events in that date range: 

WHERE lower(duration)::date <@ '[start, finish]'::daterange

In the above start and finish are user submitted parameters.

Then I want to filter those events by events that are playing during a specific time e.g. 9PM, essentially only show events that have 9PM between their start and end time.

So if I have the following table:

id    duration
---  ------------------------------------
 A    2019-12-21 19:00...2019-12-22 01:00
 B    2019-12-17 16:00...2019-12-17 18:00
 C    2019-12-23 19:00...2019-12-23 21:00
 D    2019-12-23 19:00...2019-12-24 01:00
 E    2019-12-27 14:00...2019-12-27 16:00

And the user submits a date range of 2019-12-21 to 2019-12-27 then event B will be filtered out. Then the user submits a time of 9:00PM (21:00), in which case A, C, and D will be returned.

EDIT

I was able to get it to work using the following:

WHERE duration @> (lower(duration)::date || ' 21:00:00')::timestamp

Where the 21:00 above is the user data, but it seems a bit hackish

Upvotes: 3

Views: 3226

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 246788

A tsrange contains a timestamp at 9 p.m. if and only if 9 p.m. on the starting day or 9 p.m. on the following day are part of the range.

You can use that to write your condition.

An example:

lower(r)::date + TIME '21:00' <@ r OR
(lower(r)::date + 1) + TIME '21:00' <@ r

is a test if r contains some timestamp at 9 p.m.

Upvotes: 2

klin
klin

Reputation: 121634

The user input from 2019-12-21 to 2019-12-27 at 21:00 means that he is interested in

select generate_series(timestamp '2019-12-21 21:00', '2019-12-27 21:00', '1 day') as t

          t
---------------------
 2019-12-21 21:00:00
 2019-12-22 21:00:00
 2019-12-23 21:00:00
 2019-12-24 21:00:00
 2019-12-25 21:00:00
 2019-12-26 21:00:00
 2019-12-27 21:00:00
(7 rows)

Hence you should check whether the duration column contains one of the timestamp:

select distinct e.*
from events e
cross join generate_series(timestamp '2019-12-21 21:00', '2019-12-27 21:00', '1 day') as t
where duration @> t

 id |                   duration
----+-----------------------------------------------
 A  | ["2019-12-21 19:00:00","2019-12-22 01:10:00")
 C  | ["2019-12-23 19:00:00","2019-12-23 21:10:00")
 D  | ["2019-12-23 19:00:00","2019-12-24 01:10:00")
(3 rows)

Upvotes: 2

Related Questions