Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id EEA55B62C3C for ; Fri, 3 Jun 2011 22:02:20 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 09718-02 for ; Sat, 4 Jun 2011 01:02:13 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-iy0-f174.google.com (mail-iy0-f174.google.com [209.85.210.174]) by mail.postgresql.org (Postfix) with ESMTP id AD227B62C1D for ; Fri, 3 Jun 2011 22:02:13 -0300 (ADT) Received: by iyb14 with SMTP id 14so1785971iyb.19 for ; Fri, 03 Jun 2011 18:02:13 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc:content-type:content-transfer-encoding; bh=IIDReTiczQhufr/B/oxLqaE39FTfHP733E7f+KXKI4o=; b=ixsu5zYXNbxgF0FmGE+AZlCpXO2mO1S6fX5/bTSufFOJJiSIdyOALVTOKqaXl4uwQL KVIPcMctlarDWyXqBZiDCEVT2ac0p4HcuDRzczo3FGE7SHvRRvUuof/RHvYjyWXjLZHR Bw/fCDR5Mx/OqwmFWFnpvxetsrT+BCg1ScYcU= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc:content-type:content-transfer-encoding; b=fOTGOQO7cOdMRwHNWr1nU4Z1HzJ56tOZqEHbFb2YDJS5yhzmjN5bdwT+FHprtTiYsR /2116cSwP2Odjx65omZ1/odpSrUFpMMLQJZ5+xzI8tJRjEquM0N1wDO0/4Slk2N/vfe3 nk7s/Wxr+ch9bUhGgtY7XBqCaTtdjWO20OdhA= Received: by 10.231.113.98 with SMTP id z34mr3440535ibp.188.1307149333204; Fri, 03 Jun 2011 18:02:13 -0700 (PDT) MIME-Version: 1.0 Received: by 10.231.149.132 with HTTP; Fri, 3 Jun 2011 18:01:53 -0700 (PDT) In-Reply-To: <20110603195253.GE1324@slacker.ja10629.home> References: <20110603195253.GE1324@slacker.ja10629.home> From: Kevin Crain Date: Fri, 3 Jun 2011 18:01:53 -0700 Message-ID: Subject: Re: selecting records X minutes apart To: lists-pgsql@useunix.net Cc: pgsql-sql@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.335 tagged_above=-5 required=5 tests=BAYES_00=-1.9, FREEMAIL_ENVFROM_END_DIGIT=1.553, FREEMAIL_FROM=0.001, RFC_ABUSE_POST=0.001, T_TO_NO_BRKTS_FREEMAIL=0.01 X-Spam-Level: X-Archive-Number: 201106/5 X-Sequence-Number: 35471 Will you be using a full timestamp with that or are you only concerned about hours and minutes? If you want a full timestamp do you care about the seconds? For example, do you want to be able to do this for '2011-06-01 23:59:04' and '2011-06-02 00:04:04'? On Fri, Jun 3, 2011 at 12:52 PM, wrote: > I have a table that, at a minimum, has ID and timestamp columns. =A0Recor= ds > are inserted into with random IDs and timestamps. =A0Duplicate IDs are al= lowed. > > I want to select records grouped by ID, ordered by timestamp that are X m= inutes > apart. In this case X is 5. > > Note, the intervals are not X minute wall clock intervals, they are X min= ute > intervals from the last accepted record, per-id. > > For instance here is some sample input data: > > ID =A0 =A0 =A0TS (HH:MM) > ------------------- > 0 =A0 =A0 =A0 20:00 > 1 =A0 =A0 =A0 20:03 > 1 =A0 =A0 =A0 20:04 > 0 =A0 =A0 =A0 20:05 > 1 =A0 =A0 =A0 20:05 > 0 =A0 =A0 =A0 20:08 > 1 =A0 =A0 =A0 20:09 > 0 =A0 =A0 =A0 20:10 > > I'd want the select to return: > > ID =A0 =A0 =A0TS (HH:MM) > ------------------- > 0 =A0 =A0 =A0 20:00 > 0 =A0 =A0 =A0 20:05 > 0 =A0 =A0 =A0 20:10 > 1 =A0 =A0 =A0 20:03 > 1 =A0 =A0 =A0 20:09 > > > Does my question make sense? > > Thanks in advance, > Wayne > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >