From: Gavin Flower <GavinFlower@archidevsys.co.nz>
To: pgsql-sql@postgresql.org
Subject: Re: selecting records X minutes apart
Date: Mon, 13 Jun 2011 09:50:16 +1200
Message-ID: <4DF53498.6000706@archidevsys.co.nz> (raw)
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;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: GavinFlower@archidevsys.co.nz
Subject: Re: selecting records X minutes apart
In-Reply-To: <4DF53498.6000706@archidevsys.co.nz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox