Re: Query information needed

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
TIMESTAMPWITH 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