X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 873E511F66C8 for ; Wed, 5 Apr 2006 01:37:35 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 44672-07 for ; Wed, 5 Apr 2006 01:37:34 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from web31803.mail.mud.yahoo.com (web31803.mail.mud.yahoo.com [68.142.207.66]) by postgresql.org (Postfix) with SMTP id E1A9311F6678 for ; Wed, 5 Apr 2006 01:37:32 -0300 (ADT) Received: (qmail 10042 invoked by uid 60001); 5 Apr 2006 04:37:32 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:Received:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=1gF2Bl4bj6a/sqKWEYXfyUoSVjxXBc9dsYsqlLPQ+7kyltvLldodJtPwBs1URKYXvfPtShFGPb2pafF1RWWZzLIkIJSN0l5sgSnsKhiLfNrhcikolluMWDYoBmPYL0JikBCjSXAHheQT1bm4HQc9ZV1F4ZfycABDZZs0m+XiMZA= ; Message-ID: <20060405043732.10040.qmail@web31803.mail.mud.yahoo.com> Received: from [68.235.218.116] by web31803.mail.mud.yahoo.com via HTTP; Tue, 04 Apr 2006 21:37:32 PDT Date: Tue, 4 Apr 2006 21:37:32 -0700 (PDT) From: Richard Broersma Jr Subject: Re: query to return hourly snapshot To: Michael Fuhr Cc: pgsql-sql@postgresql.org In-Reply-To: <20060405024916.GA75532@winnie.fuhr.org> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=1.138 required=5 tests=[AWL=-0.220, DNS_FROM_RFC_ABUSE=0.479, DNS_FROM_RFC_WHOIS=0.879] X-Spam-Score: 1.138 X-Spam-Level: * X-Archive-Number: 200604/16 X-Sequence-Number: 24825 Yes! Thanks you very much! --- Michael Fuhr wrote: > On Tue, Apr 04, 2006 at 05:58:26PM -0700, Richard Broersma Jr wrote: > > I am look for help in developing a query that will return the nearest > > process record that was logged at or after each hour in a day (i.e. > > hourly snapshot). > > Are you looking for something like this? > > SELECT p.process, date_trunc('hour', p.tstamp) AS hour > FROM process AS p > JOIN ( > SELECT date_trunc('hour', tstamp), min(tstamp) > FROM process > WHERE date_trunc('day', tstamp) = '2005-10-26' > GROUP BY date_trunc('hour', tstamp) > ) AS s ON s.min = p.tstamp > ORDER BY hour; > > Or, using PostgreSQL's non-standard DISTINCT ON clause: > > SELECT DISTINCT ON (date_trunc('hour', tstamp)) > process, date_trunc('hour', tstamp) AS hour > FROM process > WHERE date_trunc('day', tstamp) = '2005-10-26' > ORDER BY date_trunc('hour', tstamp), tstamp; > > -- > Michael Fuhr > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings >