Postgres SQL select a range of records spaced out by a given interval

Question

I am trying to determine if it is possible, using only sql for postgres, to select a range of time ordered records at a given interval.

Lets say I have 60 records, one record for each minute in a given hour. I want to select records at 5 minute intervals for that hour. The resulting rows should be 12 records each one 5 minutes apart.

This is currently accomplished by selecting the full range of records and then looping thru the results and pulling out the records at the given interval. I am trying to see if I can do this purly in sql as our db is large and we may be dealing with tens of thousands of records.

Any thoughts?

Answer 1

Yes you can. Its really easy once you get the hang of it. I think its one of jewels of SQL and its especially easy in PostgreSQL because of its excellent temporal support. Often, complex functions can turn into very simple queries in SQL that can scale and be indexed properly.

This uses generate_series to draw up sample time stamps that are spaced 1 minute apart. The outer query then extracts the minute and uses modulo to find the values that are 5 minutes apart.

select
    ts,
    extract(minute from ts)::integer as minute

    from
    ( -- generate some time stamps - one minute apart
        select
            current_time + (n || ' minute')::interval  as ts
        from generate_series(1, 30) as n
    ) as timestamps
    -- extract the minute check if its on a 5 minute interval
    where extract(minute from ts)::integer % 5 = 0
    -- only pick this hour 
    and extract(hour from ts) = extract(hour from current_time)
;
         ts         | minute 
--------------------+--------
 19:40:53.508836-07 |     40
 19:45:53.508836-07 |     45
 19:50:53.508836-07 |     50
 19:55:53.508836-07 |     55

Notice how you could add an computed index on the where clause (where the value of the expression would make up the index) could lead to major speed improvements. Maybe not very selective in this case, but good to be aware of.

I wrote a reservation system once in PostgreSQL (which had lots of temporal logic where date intervals could not overlap) and never had to resort to iterative methods.

http://www.amazon.com/SQL-Design-Patterns-Programming-Focus/dp/0977671542 is an excellent book that goes has lots of interval examples. Hard to find in book stores now but well worth it.