X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 6CB35D8B05 for ; Wed, 19 Oct 2005 04:43:59 -0300 (ADT) 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 62809-09 for ; Wed, 19 Oct 2005 07:43:56 +0000 (GMT) Received: from anchor-post-34.mail.demon.net (anchor-post-34.mail.demon.net [194.217.242.92]) by svr1.postgresql.org (Postfix) with ESMTP id 8AC22D7B74 for ; Wed, 19 Oct 2005 04:43:57 -0300 (ADT) Received: from lfix.demon.co.uk ([80.177.205.209] helo=cerberus.lfix.co.uk) by anchor-post-34.mail.demon.net with esmtp (Exim 4.42) id 1ES8cA-000HnX-Em; Wed, 19 Oct 2005 07:43:58 +0000 Received: from linda.lfix.co.uk ([192.168.1.1]) by cerberus.lfix.co.uk with esmtp (Exim 4.50) id 1ES8c5-0001SL-Bz; Wed, 19 Oct 2005 08:43:53 +0100 Subject: Re: Query information needed From: Oliver Elphick Reply-To: olly@lfix.co.uk To: djzanky@gmail.com Cc: pgsql-sql@postgresql.org In-Reply-To: <1129207846.673463.305670@g44g2000cwa.googlegroups.com> References: <1129207846.673463.305670@g44g2000cwa.googlegroups.com> Content-Type: text/plain Organization: LFIX Limited Date: Wed, 19 Oct 2005 08:44:01 +0100 Message-Id: <1129707841.19925.4.camel@linda.lfix.co.uk> Mime-Version: 1.0 X-Mailer: Evolution 2.2.3 Content-Transfer-Encoding: 7bit X-LFIX-MailScanner: Found to be clean X-LFIX-MailScanner-SpamCheck: not spam, SpamAssassin (score=-5.899, required 5, autolearn=not spam, ALL_TRUSTED -3.30, BAYES_00 -2.60) X-LFIX-MailScanner-From: olly@lfix.co.uk X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.055 required=5 tests=[AWL=0.005, FORGED_RCVD_HELO=0.05] X-Spam-Level: X-Archive-Number: 200510/215 X-Sequence-Number: 23068 On Thu, 2005-10-13 at 05:50 -0700, djzanky@gmail.com wrote: > Dear all, > > I have a table created with this specifications: > > CREATE TABLE cdr ( > calldate timestamp with time zone NOT NULL default now(), > clid varchar(80) NOT NULL default '', > src varchar(80) NOT NULL default '', > dst varchar(80) NOT NULL default '', > dcontext varchar(80) NOT NULL default '', > channel varchar(80) NOT NULL default '', > dstchannel varchar(80) NOT NULL default '', > lastapp varchar(80) NOT NULL default '', > lastdata varchar(80) NOT NULL default '', > duration bigint NOT NULL default '0', > billsec bigint NOT NULL default '0', > disposition varchar(45) NOT NULL default '', > amaflags bigint NOT NULL default '0', > accountcode varchar(20) NOT NULL default '', > uniqueid varchar(32) NOT NULL default '', > userfield varchar(255) NOT NULL default '' > ); > > I want to extract the number of calls placed in 1 hour and the average > call duration > > I'm working with this query: > > SELECT date_trunc('hour',calldate), duration FROM cdr WHERE src=601 > ORDER BY calldate; > > i tried several other queries but i'm not able to count the number of > calls in an hour (better in a time interval) and calculate the average > duration. For any particular interval: SELECT COUNT(*), AVG(duration) FROM cdr WHERE src='601' AND calldate BETWEEN CAST ('2005-10-17 10:00:00' AS TIMESTAMP WITH TIMEZONE) AND CAST ('2005-10-17 10:59:59' AS TIMESTAMP WITH TIMEZONE); -- Oliver Elphick olly@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA ======================================== Do you want to know God? http://www.lfix.co.uk/knowing_god.html