Let's a take a look at just the input set for ID 0. 0 20:00 0 20:05 0 20:08 0 20:10
I want records, starting from the oldest record (20:00), that are at least 5 minutes apart. So 20:00, 20:05, 20:10 but 20:08 - 20:05 is only 3 minutes so it is to be ignored. I was hoping to do this with a single SQL query that renders good runtime performance but it may not possible. But I'm by no means proficient in SQL. On Sat, Jun 04, 2011 at 05:51:18PM -0700, Kevin Crain wrote: > Why is (0,20:10) listed in your expected results when there is a (0,20:08)? > > > 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
