Postgres Pro
Downloads
Home   >   mailing lists

Thread: sum of a time column

sum of a time column

From
arief#
Date:
Dear all,


I'm sorry if this sounds stupid or have been talked about before.

Suppose I have a field in my table that's called duration with type
'time without timezone'. How do I do sum on this field based on another
field let say called dateofevent?

SQL: SELECT SUM(duration) FROM durtable GROUP BY dateofevent;

gives me:

ERROR:  Unable to select an aggregate function sum(time without time
zone)

Is this because of me still using PostgreSQL version 7.2.1 from Debian
Woody? Or some other stupidity?


TIA, Regards.
-arief

Re: sum of a time column

From
Pavel Stehule
Date:
Hello,

you can sum only interval type

create type x (a inteval);

testdb011=> insert into x values ('1 day'::interval);
INSERT 18871 1
testdb011=> insert into x values ('1 day'::interval);
INSERT 18872 1
testdb011=> insert into x values ('1 day'::interval);
INSERT 18873 1
testdb011=> insert into x values ('1 day'::interval);
INSERT 18874 1
testdb011=> insert into x values ('1 day'::interval);
INSERT 18875 1
testdb011=> insert into x values ('1 day'::interval);
testdb011=> select sum(i) from x;
  sum
--------
 6 days


regards
Pavel Stehule

On Fri, 5 Mar 2004, arief# wrote:

> Dear all,
>
>
> I'm sorry if this sounds stupid or have been talked about before.
>
> Suppose I have a field in my table that's called duration with type
> 'time without timezone'. How do I do sum on this field based on another
> field let say called dateofevent?
>
> SQL: SELECT SUM(duration) FROM durtable GROUP BY dateofevent;
>
> gives me:
>
> ERROR:  Unable to select an aggregate function sum(time without time
> zone)
>
> Is this because of me still using PostgreSQL version 7.2.1 from Debian
> Woody? Or some other stupidity?
>
>
> TIA, Regards.
> -arief
>
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

Re: sum of a time column

From
Tom Lane
Date:
arief# <arief_m_utama@telkomsel.co.id> writes:
> Suppose I have a field in my table that's called duration with type
> 'time without timezone'. How do I do sum on this field based on another
> field let say called dateofevent?

> SQL: SELECT SUM(duration) FROM durtable GROUP BY dateofevent;
> ERROR:  Unable to select an aggregate function sum(time without time
> zone)

There are neither addition nor SUM() operations for the time type,
because it is not logically sensible to add two times of day.

It seems that you may be using the wrong datatype --- perhaps the way
you are using the field is really as an interval?

If you're really intent on using the time type here, you can cast it
to interval:
    SELECT SUM(duration::interval) FROM ...

            regards, tom lane

Re: sum of a time column

From
"scott.marlowe"
Date:
On Fri, 5 Mar 2004, arief# wrote:

> Dear all,
>
>
> I'm sorry if this sounds stupid or have been talked about before.
>
> Suppose I have a field in my table that's called duration with type
> 'time without timezone'. How do I do sum on this field based on another
> field let say called dateofevent?
>
> SQL: SELECT SUM(duration) FROM durtable GROUP BY dateofevent;
>
> gives me:
>
> ERROR:  Unable to select an aggregate function sum(time without time
> zone)
>
> Is this because of me still using PostgreSQL version 7.2.1 from Debian
> Woody? Or some other stupidity?

No, there's just no logical way to add dates.  If you want to find the
total number of days those dates are after a certain date, you could do
something like:

select sum('2005-01-01 00:00:00'-dt) from table;