From: Oliver Elphick <olly@lfix.co.uk>
To: djzanky@gmail.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Query information needed
Date: Wed, 19 Oct 2005 08:44:01 +0100
Message-ID: <1129707841.19925.4.camel@linda.lfix.co.uk> (raw)
In-Reply-To: <1129207846.673463.305670@g44g2000cwa.googlegroups.com>
References: <1129207846.673463.305670@g44g2000cwa.googlegroups.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: olly@lfix.co.uk, djzanky@gmail.com
Subject: Re: Query information needed
In-Reply-To: <1129707841.19925.4.camel@linda.lfix.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox