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
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
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;