1

I want to query all events that are going on right now. Each events record has at and minutes fields for the time range of the event. I might query the events table like this:

SELECT * FROM events WHERE at >= NOW() - INTERVAL '2 hours' AND at <= NOW()

This assumes the event is 2 hours long. But I don't know that. The actual length is stored in the minutes field. How can I use that field in my query? Or is there a better mechanism for me? I've unsuccessfully tried things like this:

SELECT * FROM events WHERE at >= NOW() - INTERVAL minutes ' minutes' AND at <= NOW()

One little caveat is that performance does matter, somewhat. There is a large number of events in our database and that is growing fast.

1

You can use:

WHERE at >= NOW() - minute * INTERVAL '1 minute' AND at <= NOW()

The Postgres documentation page has similar examples: Date/Time Functions and Operators

The above is not going to be very efficient but you could add an index on at + minute * INTERVAL '1 minute' and then modify the query condition:

WHERE at <= NOW() AND (at + minute * INTERVAL '1 minute') >= NOW()

But it would probably be better if you used a gist index on the range (from when the event starts until it finishes), with something like this:

CREATE INDEX event_start_end_idx ON events 
  USING gist (tsrange(at, at + minute * INTERVAL '1 minute'));

and then use the "contains" (@>) range operator:

WHERE tsrange(at, at + minute * INTERVAL '1 minute'))  @>  NOW()
  • excellent! now is this going to always have to scan every single row? Seems like it. Any way to do this without scanning every row? – at. Jun 3 '15 at 17:36
  • I think the at <= NOW() will be able to be used if there is an index on at (so maybe not a full table scan if you have data with future dates). If (as expected) all data are in the past, you could create an index on an expression. Editing the answer. – ypercubeᵀᴹ Jun 3 '15 at 17:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.