Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id E9EA3B88927 for ; Sat, 4 Jun 2011 15:59:15 -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 91068-04 for ; Sat, 4 Jun 2011 18:59:02 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from eastrmfepo203.cox.net (eastrmfepo203.cox.net [68.230.241.218]) by mail.postgresql.org (Postfix) with ESMTP id A399FB88926 for ; Sat, 4 Jun 2011 15:59:02 -0300 (ADT) Received: from eastrmimpo03.cox.net ([68.1.16.126]) by eastrmfepo203.cox.net (InterMail vM.8.01.04.00 201-2260-137-20101110) with ESMTP id <20110604185858.YWJM32702.eastrmfepo203.cox.net@eastrmimpo03.cox.net> for ; Sat, 4 Jun 2011 14:58:58 -0400 Received: from slacker.ja10629.home ([68.106.109.147]) by eastrmimpo03.cox.net with bizsmtp id ruyn1g00B3Ar1Vg02uyn22; Sat, 04 Jun 2011 14:58:58 -0400 X-CT-Score: 0.00 X-CT-RefID: str=0001.0A020205.4DEA8072.0032,ss=1,re=0.000,fgs=0 X-CT-Spam: 0 X-Authority-Analysis: v=1.1 cv=DkLg0PgY2o4lZqvIebXfT14XCAMsRf7aiPsXd65v5So= c=1 sm=1 a=z1TLwsU0kBEA:10 a=rNODZyWf3PYA:10 a=PjkiJtDTOQ4A:10 a=ZcFhQy0-F_sA:10 a=8nJEP1OIZ-IA:10 a=sXZhTwfwSKhiTs9RzZXpNQ==:17 a=P6M1L9rJAAAA:8 a=epTmVMiNAAAA:8 a=faMODaxYTJKRGeCtQyYA:9 a=6ATu1QHcmCs9u58XX0IA:7 a=wPNLvfGTeEIA:10 a=V_iEmhlNuOsA:10 a=YObXiYvaVLwA:10 a=sXZhTwfwSKhiTs9RzZXpNQ==:117 X-CM-Score: 0.00 Authentication-Results: cox.net; none Received: from wcuddy by slacker.ja10629.home with local (Exim 4.72) (envelope-from ) id 1QSw3b-0004Dl-Ft for pgsql-sql@postgresql.org; Sat, 04 Jun 2011 14:58:47 -0400 Date: Sat, 4 Jun 2011 14:58:47 -0400 From: lists-pgsql@useunix.net To: pgsql-sql@postgresql.org Subject: Re: selecting records X minutes apart Message-ID: <20110604185847.GF1324@slacker.ja10629.home> References: <20110603195253.GE1324@slacker.ja10629.home> Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: User-Agent: Mutt/1.4.2.3i X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-5 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201106/8 X-Sequence-Number: 35474 The TS column type is actually a timestamp with out timezone and yes I want to take seconds into account so both of your entries would be included in the result. On Fri, Jun 03, 2011 at 06:01:53PM -0700, Kevin Crain wrote: > 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.  Records > > are inserted into with random IDs and timestamps.  Duplicate IDs are allowed. > > > > I want to select records grouped by ID, ordered by timestamp that are X minutes > > apart. In this case X is 5. > > > > Note, the intervals are not X minute wall clock intervals, they are X minute > > intervals from the last accepted record, per-id. > > > > For instance here is some sample input data: > > > > ID      TS (HH:MM) > > ------------------- > > 0       20:00 > > 1       20:03 > > 1       20:04 > > 0       20:05 > > 1       20:05 > > 0       20:08 > > 1       20:09 > > 0       20:10 > > > > I'd want the select to return: > > > > ID      TS (HH:MM) > > ------------------- > > 0       20:00 > > 0       20:05 > > 0       20:10 > > 1       20:03 > > 1       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 > >