Thomas Lockhart <lockhart@fourpalms.org> writes:
>> timetest=# select sum(timeend-timebegin) as totaltime from timetest;
>> ERROR: Unable to select an aggregate function sum(time)
> Hmm. The underlying math works:
> lockhart=# select time '10:01' - time '10:00';
> ----------
> 00:01:00
"Works" is in the eye of the beholder, perhaps. I would think that the
difference of two times should be an interval (which would allow the
sum() to work, since we have sum(interval)). But there is no
time-minus-time operator. What actually appears to be happening is that
the system casts the second time to an interval and then applies the
time-minus-interval operator, yielding a time. There is no sum(time)
aggregate, and shouldn't be IMHO.
In short, I think the missing piece is not sum(time) but
time-minus-time. Maybe that's what you think too, but it wasn't clear.
BTW, I'm unconvinced that an implicit cast from time to interval is a
good idea... what's the point of maintaining a datatype distinction
between timestamp-like types and interval-like types if we will allow
implicit casts across that boundary?
regards, tom lane