WITH foo AS ( SELECT column1::integer id, column2::timestamp ts FROM (VALUES (0, '1-Jan-2010 20:00'), (1, '1-Jan-2010 20:03'), (1, '1-Jan-2010 20:04'), (0, '1-Jan-2010 20:05'), (1, '1-Jan-2010 20:05'), (0, '1-Jan-2010 20:08'), (1, '1-Jan-2010 20:09'), (0, '1-Jan-2010 20:10')) vals )
SELECT * FROM ( SELECT id, ts, ( SELECT b.ts FROM foo b WHERE b.id = a.id AND b.ts > a.ts ORDER BY b.ts LIMIT 1 ) - ts gap FROM foo a ) c ORDER BY id, ts ; Still can't make heads or tails of the needs yet. But running the snippet above may give some more ideas. Looking at id 1 tuples: 20:03 is in because it has a record >= X away. But so does 20:04. 20:04 is out because it has a record that is < X away. But so does 20:03, which is in. Etc. ----- Reply message ----- From: lists-pg...@useunix.net Date: Fri, Jun 3, 2011 4:52 pm Subject: [SQL] selecting records X minutes apart To: <pgsql-sql@postgresql.org> 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
