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@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