pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsum(time) problem
6+ messages / 6 participants
[nested] [flat]
* sum(time) problem
@ 2003-01-17 13:03 Oliver Vecernik <vecernik@aon.at>
0 siblings, 1 reply; 6+ messages in thread
From: Oliver Vecernik @ 2003-01-17 13:03 UTC (permalink / raw)
To: pgsql-sql
Hi all,
I've got following table structure:
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?
Oliver
--
VECERNIK Datenerfassungssysteme
A-2560 Hernstein, Hofkogelgasse 17
Tel.: +43 2633 47530, Fax: DW 50
http://members.aon.at/vecernik
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: sum(time) problem
@ 2003-01-17 13:13 Julian Scarfe <julian.scarfe@ntlworld.com>
parent: Oliver Vecernik <vecernik@aon.at>
0 siblings, 1 reply; 6+ messages in thread
From: Julian Scarfe @ 2003-01-17 13:13 UTC (permalink / raw)
To: Oliver Vecernik <vecernik@aon.at>; pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: sum(time) problem
@ 2003-01-17 15:03 Tom Lane <tgl@sss.pgh.pa.us>
parent: Julian Scarfe <julian.scarfe@ntlworld.com>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2003-01-17 15:03 UTC (permalink / raw)
To: Julian Scarfe <julian.scarfe@ntlworld.com>; +Cc: Oliver Vecernik <vecernik@aon.at>; pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: sum ( time) problem
@ 2004-05-27 11:14 Willem de Jong <Willumpie@xs4all.nl>
0 siblings, 1 reply; 6+ messages in thread
From: Willem de Jong @ 2004-05-27 11:14 UTC (permalink / raw)
To: pgsql-sql
"Loeke" <LoekeDoeLoekeDoe@dontlikespam.hotmail.com> wrote in
Dan heb je nog de 24-uurs notatie, en krijg je niet een result boven de
24-uurs notatie.
news:1084406549.228484@seven.kulnet.kuleuven.ac.be:
> kijk eens bij functions and operators van de
> sql help bij pgadmin
>
> "Willem de Jong" <Willumpie@xs4all.nl> wrote in message
> news:Xns94E7AEA1D11BBJongdatdnaservicesdo@200.46.204.72...
>> Hello,
>>
>> If i do a sum(time) the result is like this '1 day 18:00:00'. But i'd
>> like to get a result like this '42:00:00'.
>>
>> How can i realise is by a query?
>>
>> thanks in advance,
>>
>> Willem de Jong.
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: sum ( time) problem
@ 2004-06-01 14:03 Bruno Wolff III <bruno@wolff.to>
parent: Willem de Jong <Willumpie@xs4all.nl>
0 siblings, 1 reply; 6+ messages in thread
From: Bruno Wolff III @ 2004-06-01 14:03 UTC (permalink / raw)
To: Willem de Jong <Willumpie@xs4all.nl>; +Cc: pgsql-sql
On Thu, May 27, 2004 at 11:14:58 +0000,
Willem de Jong <Willumpie@xs4all.nl> wrote:
> >>
> >> If i do a sum(time) the result is like this '1 day 18:00:00'. But i'd
> >> like to get a result like this '42:00:00'.
> >>
> >> How can i realise is by a query?
You can do something like the following: (not completely tested)
select extract(epoch from sum(time))/3600 || ':' ||
extract(minutes from sum(time)) || ':' ||
extract(seconds from sum(time));
I am not sure if the above will work right for negative intervals.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: sum ( time) problem
@ 2004-06-07 06:09 Willem de Jong <Willumpie@NOSPAM.xs4all.nl>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 6+ messages in thread
From: Willem de Jong @ 2004-06-07 06:09 UTC (permalink / raw)
To: pgsql-sql
I'm afraid it is the only solution...
Thanks for reply
bruno@wolff.to (Bruno Wolff III) wrote in news:20040601140334.GA24188
@wolff.to:
> On Thu, May 27, 2004 at 11:14:58 +0000,
> Willem de Jong <Willumpie@xs4all.nl> wrote:
>> >>
>> >> If i do a sum(time) the result is like this '1 day 18:00:00'. But i'd
>> >> like to get a result like this '42:00:00'.
>> >>
>> >> How can i realise is by a query?
>
> You can do something like the following: (not completely tested)
> select extract(epoch from sum(time))/3600 || ':' ||
> extract(minutes from sum(time)) || ':' ||
> extract(seconds from sum(time));
>
> I am not sure if the above will work right for negative intervals.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2004-06-07 06:09 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-01-17 13:03 sum(time) problem Oliver Vecernik <vecernik@aon.at>
2003-01-17 13:13 ` Julian Scarfe <julian.scarfe@ntlworld.com>
2003-01-17 15:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-05-27 11:14 Re: sum ( time) problem Willem de Jong <Willumpie@xs4all.nl>
2004-06-01 14:03 ` Re: sum ( time) problem Bruno Wolff III <bruno@wolff.to>
2004-06-07 06:09 ` Re: sum ( time) problem Willem de Jong <Willumpie@NOSPAM.xs4all.nl>
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