Thread: sum(time) problem
Hi all, I've got following table structure: sport=# \d polar Table "polar"Column | Type | Modifiers --------+--------------------------+-----------ts | timestamp with time zone | not nulltime | time without time zone |sport | integer | default 1kcal | 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 | 3882001-05-18 17:08:11+02| 00:42:36 | 1 | 5952001-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? Oliver -- VECERNIK Datenerfassungssysteme A-2560 Hernstein, Hofkogelgasse 17 Tel.: +43 2633 47530, Fax: DW 50 http://members.aon.at/vecernik
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
Julian Scarfe <julian.scarfe@ntlworld.com> writes: >> 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. Right. "time" is intended for time-of-day values; so taking the sum would be a nonsensical operation. regards, tom lane