From: | Julian Scarfe <julian(dot)scarfe(at)ntlworld(dot)com> |
---|---|
To: | Oliver Vecernik <vecernik(at)aon(dot)at>, <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: sum(time) problem |
Date: | 2003-01-17 13:13:31 |
Message-ID: | BA4DB1FB.204D0%julian.scarfe@ntlworld.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On 17/1/03 13:03, "Oliver Vecernik" <vecernik(at)aon(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2003-01-17 15:03:04 | Re: sum(time) problem |
Previous Message | Oliver Vecernik | 2003-01-17 13:03:51 | sum(time) problem |