User-Agent: Microsoft-Entourage/10.1.1.2418
Date: Fri, 17 Jan 2003 13:13:31 +0000
Subject: Re: sum(time) problem
From: Julian Scarfe <julian.scarfe@ntlworld.com>
To: Oliver Vecernik <vecernik@aon.at>, <pgsql-sql@postgresql.org>
Message-ID: <BA4DB1FB.204D0%julian.scarfe@ntlworld.com>
In-Reply-To: <3E27FF37.4060803@aon.at>
Mime-version: 1.0
Content-type: text/plain; charset="US-ASCII"
Content-transfer-encoding: 7bit

On 17/1/03 13:03, "Oliver Vecernik" <vecernik@aon.at> wrote:

> sport=# \d polar
>                Table "polar"
> Column |           Type           | Modifiers
> --------+--------------------------+-----------
> ts     | timestamp with time zone | not null
> time   | time without time zone   |
> sport  | integer                  | default 1
> kcal   | integer                  |
> Primary key: polar_pkey
> 
> sport=# select * from polar limit 3;
>          ts           |   time   | sport | kcal
> ------------------------+----------+-------+------
> 2001-05-17 19:47:31+02 | 00:28:25 |     1 |  388
> 2001-05-18 17:08:11+02 | 00:42:36 |     1 |  595
> 2001-05-19 13:41:43+02 | 00:51:39 |     1 |  737
> (3 rows)
> 
> 
> I'd like to have the total amount of time:
> 
> sport=# select sum(time) from polar where extract(year from ts)=2001;
> ERROR:  Unable to select an aggregate function sum(time without time zone)
> 
> It seems I've chosen the wrong type. Or is there another solution?

Correct diagnosis.  You need the "interval" type, not the "time" type for
your second field.  Interval is a time difference between two timestamps,
for example the time between the start and the finish of a race.

If you check out the available aggregates with \da you'll find that you can
sum an interval, but not a time.

Julian Scarfe