Re: selecting records X minutes apart

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.