Bug #443: Problem with time functions.
Alessandro Rossi (alex@sunrise.radiostudiodelta.it>) reports a bug with a severity of 2
The lower the number the more severe it is.
Short Description
Problem with time functions.
Long Description
Here is the DB and some sample entries.
CREATE TABLE "timetest" (
"id" serial primary key,
"timebegin" time,
"timeend" time
);
insert into timetest (timebegin,timeend) values ('12:00:20','12:01:00');
insert into timetest (timebegin,timeend) values ('14:00:20','14:02:00');
insert into timetest (timebegin,timeend) values ('15:00:00','15:00:40');
THIS SELECT DOESN'T WORK:
select sum(timeend-timebegin) as totaltime from timetest;
HERE IS WHAT POSTGRES REPORT:
timetest=# select sum(timeend-timebegin) as totaltime from timetest;
ERROR: Unable to select an aggregate function sum(time)
timetest=# select version();
version
-------------------------------------------------------------
PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)
timetest=#
ON POSTGRES 7.0.3
timetest=# select sum(timeend-timebegin) as totaltime from timetest;
totaltime
-----------
00:03
(1 row)
timetest=# select version();
version
-------------------------------------------------------------
PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc 2.96
(1 row)
Sample Code
No file was uploaded with this report
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
lockhart=# select sum(time '10:01' - time '10:00');
ERROR: Unable to select an aggregate function sum(time)
A workaround for this last query is
lockhart=# select sum(cast(time '10:01' - time '10:00' as interval));
sum
-------
00:01
hth
OK. Bruce, are you likely to be keeper of a "fix list" for this beta
cycle? This issue should be on it...
- Thomas
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
Hmm. The underlying math works:
...
"Works" is in the eye of the beholder, perhaps.
Of course ;)
... 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.
Well, I'd have been willing to think that, if I had known that.
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?
I'm not certain whether this was to fix a particular issue or just
proactive mucking around.
Either way, a time-time operator seems like a good idea, until proven
otherwise. Will try to get it on my list for the current work.
- Thomas