Message-ID: <4DF53498.6000706@archidevsys.co.nz>
Date: Mon, 13 Jun 2011 09:50:16 +1200
From: Gavin Flower <GavinFlower@archidevsys.co.nz>
Organization: ArchiDevSys
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.17) Gecko/20110428 Fedora/3.1.10-1.fc14
	Lightning/1.0b3pre Thunderbird/3.1.10
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: selecting records X minutes apart
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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;