postgresql search in time ranges

Question

So here's what I'm trying to do:

I have a number of events, each event represents a datetime range (like 07.03.2014 06:00 - 07.03.2014 08:00) event is associated (one-to-many) to hall_schedule.

Then I want to search for all hall_schedule's that don't have events that cover some specific time range, for example:

"I want all hall_schedules where hall events don't cover the 07.03.2014 06:00 - 07.03.2014 08:00 range"

So the question is, in order to get the best performance, how do I design this? What is the best way to store events and later do the described search?

I'd be glad to use something specific to postgresql (9.3), whatever would give the best performance

Cover is a fuzzy word here. Edit your question, and include CREATE TABLE and INSERT statements for some sample data. — Mike Sherrill 'Cat Recall'
– Mike Sherrill 'Cat Recall', Commented Mar 7, 2014 at 12:01
See Optimizing queries on a range of timestamps (two columns) and 2 B-tree indices OR 1 GiST index on tsrange — which will perform better? on dba.SE — Daniel Vérité
– Daniel Vérité, Commented Mar 7, 2014 at 13:04

pozs · Accepted Answer · 2014-03-07 13:42:53Z

5

use TIMESTAMP WITH TIME ZONE for hall_schedule's start and end time points

use tstzrange to detect overlappings, like:

SELECT *
  FROM "hall_schedule"
 WHERE NOT (tstzrange("start", "end", '[]') &&
            tstzrange(:input_start, :input_end, '[]'))

Notes:

you could store the whole range in a tstzrange column within the hall_schedule table, if you want, but it's harder to edit from the application (you must supply its text representation everywhere)
this way, you can actually create EXCLUDE constraints too, to prevent events in hall_schedule to overlap each other, if you want.

Update:

Additionally, to speed up your queries, you can add the following index too:

CREATE INDEX "hall_schedule_range_idx"
          ON "hall_schedule"
       USING gist ((tstzrange("start", "end", '[]')));

edited Mar 7, 2014 at 13:42

answered Mar 7, 2014 at 13:04

pozs

36.6k5 gold badges61 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgresql search in time ranges

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related