Thread: Time difference changed in 7.2 (3rd time post and hoping for the best :)

Hi,

    The difference between two times (interval) seems to have undergone a
change from 7.1 to 7.2. For example

'1:00'::time - '23:00'::time = @ 2 hours in 7.1 and;
'1:00'::time - '23:00'::time = @ 22 hours ago in 7.2.

I assume there must be some sound reason for this change, but I have
used time differences at loads of places in my considerably large
application and the whole thing goes (not surprisingly) completely
haywire.

Is there some way I can revert to the old behaviour? Downgrading my
postgres version is not a choice :). IAC, since

'23:00'::time + '1:00'::interval = '1:00'::time,

shouldn't the old behaviour be considered the more consistent one?

Thanks for any advice and help. Its critical that I get my application
up and running as quickly as possible.

Many thanks and regards,
Gurunandan

> The difference between two times (interval) seems to have undergone a
> change from 7.1 to 7.2.

In 7.1 and earlier, the "time - time" operation was converted to "time -
interval" which returned a time. So the result was normalized to a
positive time of day.

In 7.2, we have an explicit operator (and function) defined to do "time
- time", and it returns an interval. Since intervals are allowed to be
signed, it does not normalize back to a positive value.

I *think* that is better behavior, but that does not help your problem.
If you really need the old behavior (which was really by omission, not
by design) then you could modify the routine "time_mi_time", probably
defined in src/backend/utils/adt/date.c.

There is a cast function available in 7.2 to force an interval back to a
time, but it seems to return NULL if the interval is negative. So you
would have to wrap that in a case statement to ensure a positive value
for input.

I'm happy to continue discussing what the *correct* behavior should be,
though if there is some guidance in SQL99 we would try to follow that.

                   - Thomas