Re: selecting records X minutes apart - Mailing list pgsql-sql

How about this (that does not require special functions nor triggers:


DROP TABLE IF EXISTS val;

CREATE TABLE val
(    id int,    ts timestamp
);

INSERT INTO val    VALUES    (0, '1-Jan-2010 20:00'),    (1, '1-Jan-2010 20:03'),    (1, '1-Jan-2010 20:04'),    (0,
'1-Jan-201020:05'),    (1, '1-Jan-2010 20:05'),    (0, '1-Jan-2010 20:08'),    (1, '1-Jan-2010 20:09'),    (0,
'1-Jan-201020:10');
 

WITH val_first AS
(    SELECT        id,        min(ts) AS ts    FROM        val    GROUP BY        id
)
SELECT    v.id,    v.ts::time
FROM    val v,    val_first vf
WHERE    v.id = vf.id AND    EXTRACT(EPOCH FROM v.ts - vf.ts)::int % 300 = 0
ORDER BY    id,    ts;