Thread: 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