Re: selecting records X minutes apart

On Sat, Jun 4, 2011 at 6:36 PM, <lists-pgsql(at)useunix(dot)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
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.