DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=huGSv3ZkiImsh1UC14jwDacBgdri81l1teTRw6Ardo3PnvWw9AGhL5hJ30xzklgeZK
	/lscnW4LvFThv9TxtYfog/j+eXVZfxIzYvnhaZVQ+yDV2dDirvrXnYDTp53OAe/mcoWN
	81oCZz2k3m+CoPUglUL58Dq8aqIw6uk+JZIp8=
MIME-Version: 1.0
In-Reply-To: <20110605013623.GC16254@slacker.ja10629.home>
References: <20110603195253.GE1324@slacker.ja10629.home>
	<BANLkTikyu3jbxKucoyr_RH-2G6Ouq1H9oA@mail.gmail.com>
	<20110605013623.GC16254@slacker.ja10629.home>
Date: Sat, 4 Jun 2011 19:10:46 -0700
Message-ID: <BANLkTikWBFevHv9igfGVKNpx792PuKY+KA@mail.gmail.com>
Subject: Re: selecting records X minutes apart
From: Richard Broersma <richard.broersma@gmail.com>
To: lists-pgsql@useunix.net
Cc: pgsql-sql@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

On Sat, Jun 4, 2011 at 6:36 PM,  <lists-pgsql@useunix.net> wrote:

> 0 =A0 =A0 =A0 20:00
> 0 =A0 =A0 =A0 20:05
> 0 =A0 =A0 =A0 20:08
> 0 =A0 =A0 =A0 20:10
>
> I want records, starting from the oldest record (20:00), that are at leas=
t 5
> minutes apart. =A0So 20:00, 20:05, 20:10 but 20:08 - 20:05 is only 3 minu=
tes so
> it is to be ignored.

Here is my next guess: --although I guess my use of WITH Recursive is corre=
ct

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 =3D Accum_ts.id
               AND B.ts >=3D Accum_ts.ts - INTERVAL '5 MINUTES'
          GROUP BY B.id
              HAVING MIN( B.ts ) <=3D MAX( B.ts ))

  SELECT id, ts
    FROM Accum_ts
ORDER BY id, ts;


--=20
Regards,
Richard Broersma Jr.