current community

your communities

more stack exchange communities

Stack Exchange Inbox Reputation and Badges
tour help
Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

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.

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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()
share|improve this answer
    
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 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 at 17:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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