On Sat, Jun 4, 2011 at 6:36 PM, <[email protected]> wrote:
> 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.
Here is my next guess: --although I guess my use of WITH Recursive is correct
WITH RECURSIVE Accum_ts( id, ts ) AS
( SELECT id, MIN( ts )
FROM Yourtable
GROUP BY id )
UNION ALL ( SELECT B.id, MIN( B.ts )
FROM Yourtable AS B
WHERE B.id = Accum_ts.id
AND B.ts >= Accum_ts.ts - INTERVAL '5 MINUTES'
GROUP BY B.id
HAVING MIN( B.ts ) <= MAX( B.ts ))
SELECT id, ts
FROM Accum_ts
ORDER BY id, ts;
--
Regards,
Richard Broersma Jr.
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
