By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,420 Members | 1,242 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,420 IT Pros & Developers. It's quick & easy.

sum of a time column

P: n/a
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 ma*******@postgresql.org

Nov 23 '05 #1
Share this Question
Share on Google+
3 Replies

P: n/a
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 ma*******@postgresql.org

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

Nov 23 '05 #2

P: n/a
arief# <ar***********@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

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

Nov 23 '05 #3

P: n/a
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;
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.