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 8D11B11F6217 for ; Tue, 4 Apr 2006 21:58:28 -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 99683-03 for ; Tue, 4 Apr 2006 21:58:28 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from web31814.mail.mud.yahoo.com (web31814.mail.mud.yahoo.com [68.142.206.167]) by postgresql.org (Postfix) with SMTP id 203A811F620D for ; Tue, 4 Apr 2006 21:58:25 -0300 (ADT) Received: (qmail 45348 invoked by uid 60001); 5 Apr 2006 00:58:26 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=i467pzL9Dlw6qyrSABqMcCkwxQKKgwfRFQmi91AT/rGUKA7qWk2KMLm02d9N9M8ElfDBEdOaLgd5yJajw+iLcBDNf59TJedcFgz3hfFnPuMy10YU+bxs1XpmmApBeXw0tKZrPXChVN6kKtupVg8V1EV3HCspv/fzuRzwwIm/bJw= ; Message-ID: <20060405005826.45346.qmail@web31814.mail.mud.yahoo.com> Received: from [68.235.218.116] by web31814.mail.mud.yahoo.com via HTTP; Tue, 04 Apr 2006 17:58:26 PDT Date: Tue, 4 Apr 2006 17:58:26 -0700 (PDT) From: Richard Broersma Jr Subject: query to return hourly snapshot To: pgsql-sql@postgresql.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/14 X-Sequence-Number: 24823 I orginally sent this email to the [novice] list but did not get any response. 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). Below are typical sample data. Also, I've included a query used to return the average of the process value for each hour to give an example for the format that I am trying to achieve. Thanks for the help. Regards, Richard select * from process limit 10; tstamp | process ---------------------+--------- 2005-10-25 21:10:41 | 3.56513 2005-10-25 21:10:42 | 3.56503 2005-10-25 21:10:43 | 3.56494 2005-10-25 21:10:44 | 3.56484 2005-10-25 21:10:45 | 3.56475 2005-10-25 21:10:46 | 3.56465 2005-10-25 21:10:47 | 3.56455 2005-10-25 21:10:48 | 3.56446 2005-10-25 21:10:49 | 3.56436 2005-10-25 21:10:50 | 3.56427 (10 rows) select avg(process), date_trunc('hour',tstamp) as date from process where date_trunc('day', tstamp) = '2005-10-26' group by date_trunc('hour', tstamp) order by date_trunc('hour', tstamp); avg | date ------------------+--------------------- 9.79195118032606 | 2005-10-26 05:00:00 10.0249767947376 | 2005-10-26 06:00:00 8.88596018049452 | 2005-10-26 07:00:00 7.95090951088542 | 2005-10-26 08:00:00 8.10741349776586 | 2005-10-26 09:00:00 7.30079822791947 | 2005-10-26 10:00:00 7.10586501293712 | 2005-10-26 11:00:00 8.15196838166979 | 2005-10-26 12:00:00 8.26183129151662 | 2005-10-26 13:00:00 8.95141531440947 | 2005-10-26 14:00:00 10.562882253329 | 2005-10-26 15:00:00 10.8634908244445 | 2005-10-26 16:00:00 11.4077104069976 | 2005-10-26 17:00:00 12.4702264580744 | 2005-10-26 18:00:00 11.9155618293134 | 2005-10-26 19:00:00 11.5622152555012 | 2005-10-26 20:00:00 11.6527367563489 | 2005-10-26 21:00:00 10.3170960432442 | 2005-10-26 22:00:00 9.56747980806563 | 2005-10-26 23:00:00 (19 rows)