Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 47CE2B61640 for ; Sat, 4 Jun 2011 21:57:28 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 27464-08 for ; Sun, 5 Jun 2011 00:57:21 +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 2BFB4B60B6B for ; Sat, 4 Jun 2011 21:57:21 -0300 (ADT) Received: by iyb14 with SMTP id 14so2278793iyb.19 for ; Sat, 04 Jun 2011 17:57:21 -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=xRxDPRNguXqd7LYHfm267UqpmpVPfl+nlOH9ojI0zQI=; b=YWlNWS4BlQK0J+T7SZWbO6nExI8YW0LeyDbbR+ssSeAbUFdrYmPOa66jBQyrni3C/0 9ss/Vn70xvkixGjQ4XG3qq9UKilVKR2+LXejL+CytStmDCu2u0JAwwju2b8AnEBXknmp voLppeZ5HO2L+1+XZiJc12iIuibW9ozU/Az1I= 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=UJ69D/WfVkXts7OiIjq3mQhzKh68jbxl+NBJGNOaF9XdF/aabVYOmdFkZEBIoig5Kt 5DosMtBJw2hWUm0kYIxDF9BXsLy2Au5yL8YlLI1ijszoCmxdDOeu0S9UFyiY2zveHAhR JUaw9FZfsvRZxYNLvHMPvvGqViSIyfb45EYpA= Received: by 10.231.195.222 with SMTP id ed30mr5031846ibb.114.1307235439108; Sat, 04 Jun 2011 17:57:19 -0700 (PDT) MIME-Version: 1.0 Received: by 10.231.149.132 with HTTP; Sat, 4 Jun 2011 17:56:59 -0700 (PDT) In-Reply-To: <20110603195253.GE1324@slacker.ja10629.home> References: <20110603195253.GE1324@slacker.ja10629.home> From: Kevin Crain Date: Sat, 4 Jun 2011 17:56:59 -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/14 X-Sequence-Number: 35480 My approach would be to add a column for LAST_TS and place a trigger on insert that populates this new column. Then you have something you can put in your WHERE clause to test on. 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 >