Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id BF499476138
	for <pgsql-sql@postgresql.org>; Tue, 12 Nov 2002 11:08:15 -0500 (EST)
Received: from fzkmail2.fzk.de (fzkmail2.fzk.de [141.52.27.52])
	by postgresql.org (Postfix) with SMTP id 0370F47610B
	for <pgsql-sql@postgresql.org>; Tue, 12 Nov 2002 11:07:54 -0500 (EST)
Received: FROM resy5.fzk.de BY fzkmail2.fzk.de ;
 Tue Nov 12 17:07:43 2002 +0100
Received: œby rodos.fzk.de with ESMTP (8.8.6 (PHNE_17135)/8.7.1) id RAA11705; Tue, 12 Nov 2002 17:07:41 +0100 (MET)
Message-ID: <3DD1274C.8F4C0335@rodos.fzk.de>
Date: Tue, 12 Nov 2002 17:07:41 +0100
From: Christoph Haller <ch@rodos.fzk.de>
X-Mailer: Mozilla 4.06 [en] (X11; I; HP-UX B.10.20 9000/780)
MIME-Version: 1.0
To: praveen_vejandla@rediffmail.com
Cc: pgsql-sql@postgresql.org
Subject: Re: averaging interval values
Content-Type: text/plain; charset=iso-8859-2
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200211/186
X-Sequence-Number: 10328

>
> 1:01:01
> 0:25:15
> 7:09:01
> 8:09:05
>
> Is there any way to average or sum those above values using a
> function?
>
> i could not type cast the varchar(40) to interval type.
>
> How to change the datatype of field from varchar to interval?
>

Try

SELECT CAST(CAST (t2 AS TEXT) AS INTERVAL) FROM test;
SELECT AVG(CAST(CAST (t2 AS TEXT) AS INTERVAL)) FROM test;
SELECT SUM(CAST(CAST (t2 AS TEXT) AS INTERVAL)) FROM test;

It works fine on my machine, I'm using PostgreSQL 7.2.1.

Regards, Christoph