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-2010 20:05'),
(1, '1-Jan-2010 20:05'),
(0, '1-Jan-2010 20:08'),
(1, '1-Jan-2010 20:09'),
(0, '1-Jan-2010 20: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;
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
