My approach would be to add a column for LAST_TS and place a trigger on insert that populates this new column. Then you have something you can put in your WHERE clause to test on.
On Fri, Jun 3, 2011 at 12:52 PM, <lists-pg...@useunix.net> wrote: > I have a table that, at a minimum, has ID and timestamp columns. Records > are inserted into with random IDs and timestamps. Duplicate IDs are allowed. > > I want to select records grouped by ID, ordered by timestamp that are X > minutes > apart. In this case X is 5. > > Note, the intervals are not X minute wall clock intervals, they are X minute > intervals from the last accepted record, per-id. > > For instance here is some sample input data: > > ID TS (HH:MM) > ------------------- > 0 20:00 > 1 20:03 > 1 20:04 > 0 20:05 > 1 20:05 > 0 20:08 > 1 20:09 > 0 20:10 > > I'd want the select to return: > > ID TS (HH:MM) > ------------------- > 0 20:00 > 0 20:05 > 0 20:10 > 1 20:03 > 1 20:09 > > > Does my question make sense? > > Thanks in advance, > Wayne > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
