X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C285252A57 for ; Mon, 21 Mar 2005 04:30:02 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 38268-08 for ; Mon, 21 Mar 2005 04:29:58 +0000 (GMT) Received: from rwcrmhc14.comcast.net (rwcrmhc14.comcast.net [216.148.227.89]) by svr1.postgresql.org (Postfix) with ESMTP id 71CC85679F for ; Mon, 21 Mar 2005 04:29:57 +0000 (GMT) Received: from vulcan (c-24-21-103-165.client.comcast.net[24.21.103.165]) by comcast.net (rwcrmhc14) with ESMTP id <20050321042954014001i1n8e>; Mon, 21 Mar 2005 04:29:54 +0000 Received: from cygnus by vulcan with local (Exim 4.44) id 1DDEYM-00020E-Q5 for pgsql-sql@postgresql.org; Sun, 20 Mar 2005 20:30:10 -0800 Date: Sun, 20 Mar 2005 20:30:10 -0800 From: Jonathan Daugherty To: pgsql-sql@postgresql.org Subject: Re: Group by 15 Minute Steps Message-ID: <20050321043010.GC20336@vulcan.cprogrammer.org> References: <423C9FC1.1050902@mk-os.de> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <423C9FC1.1050902@mk-os.de> User-Agent: Mutt/1.5.6+20040907i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200503/270 X-Sequence-Number: 20749 # Usally, the steps between each entries is 300 seconds. How can I # group by by 15, 30, 45 minutes so that i can get averages over the # specified timeframe? For 15-minute data, I'd compute the "quadrant" of each record and group by the quadrant number. Anything that occurs from :00 to :14 is in quadrant zero, :15 - :29 is quadrant 1, etc., yielding quadrants 0-3. mydb> SELECT (time / (15 * 60)) AS quadrant, SUM(ifinoctets) FROM mytable GROUP BY quadrant ORDER BY quadrant; Divide the time by 60 to get minutes and 15 to get quadrants. You can see how to extend this for other intervals. -- Jonathan Daugherty Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Replication & Support Services, (503) 667-4564