On Sat, Jun 4, 2011 at 6:36 PM, <lists-pgsql@useunix.net> 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
ANDB.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.