sum of a time column

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

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:
[color=blue]
> 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
>[/color]


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

re: sum of a time column

arief# <arief_m_utama@telkomsel.co.id> writes:[color=blue]
> 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?[/color]
[color=blue]
> SQL: SELECT SUM(duration) FROM durtable GROUP BY dateofevent;
> ERROR: Unable to select an aggregate function sum(time without time
> zone)[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

re: sum of a time column

On Fri, 5 Mar 2004, arief# wrote:
[color=blue]
> 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?[/color]

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;


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings