selecting records X minutes apart

From: lists-pgsql(at)useunix(dot)net
To: pgsql-sql(at)postgresql(dot)org
Subject: selecting records X minutes apart
Date: 2011-06-03 19:52:53
Message-ID: 20110603195253.GE1324@slacker.ja10629.home
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Kevin Crain 2011-06-04 01:01:53 Re: selecting records X minutes apart
Previous Message Greg Sabino Mullane 2011-06-01 22:22:39 Re: 9.0+ way of determining if a LISTEN channel has a backend listening?