From: Charlie <scorpdaddy@hotmail.com>
To: lists-pgsql@useunix.net
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] selecting records X minutes apart
Date: Sat, 4 Jun 2011 17:42:33 -0300
Message-ID: <BLU0-SMTP138C0D87721A4D87DA26461CE7E0@phx.gbl> (raw)
SELECT a.*
FROM foo a
INNER JOIN foo b
ON b.id = a.id
AND b.ts >= a.ts + '5 minutes'
GROUP BY a.id, a.ts
ORDER BY a.id, a.ts
Not clear yet on the needs, but this may give a start.
----- Reply message -----
From: lists-pgsql@useunix.net
Date: Sat, Jun 4, 2011 4:15 pm
Subject: [SQL] selecting records X minutes apart
To: <pgsql-sql@postgresql.org>
On Sat, Jun 04, 2011 at 11:45:08AM +0000, Jasen Betts wrote:
> On 2011-06-03, lists-pgsql@useunix.net <lists-pgsql@useunix.net> wrote:
> >
> > ID TS (HH:MM)
> > -------------------
> > 0 20:00
> > 0 20:05
> > 0 20:10
> > 1 20:03
> > 1 20:09
> >
> >
> > Does my question make sense?
>
> no, why is (1,20:04) excluded, but (0,20:05) included?
> both records are 5 minutes from the newest.
Jasen,
(1,20:04) is excluded because it's timestamp is less than 5 minutes from the
previous record with the same ID (1,20:03), (0,20:05) is included for the
opposite reason.
Let me restate my requirement again with a little more detail. I want to
select records grouped by ID, ordered by timestamp, in ascending order so I'm
starting with the oldest, that are at least X minutes apart.
I hope that helps.
Thanks again,
Wayne
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
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: scorpdaddy@hotmail.com, lists-pgsql@useunix.net
Subject: Re: Re: [SQL] selecting records X minutes apart
In-Reply-To: <BLU0-SMTP138C0D87721A4D87DA26461CE7E0@phx.gbl>
* 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