Re: [SQL] selecting records X minutes apart

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-pgsql(at)useunix(dot)net
Date: Fri, Jun 3, 2011 4:52 pm
Subject: [SQL] selecting records X minutes apart
To: <pgsql-sql(at)postgresql(dot)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