Re: Time difference changed in 7.2 (3rd time post and

On Thu, 2002-02-21 at 23:13, Thomas Lockhart wrote:
> > 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.

Thank you for the explanation.

Effectively, what the old definition did was to assume that in the
expression (t1::time - t2::time), t1 was always *temporally later* than
t2. If t1 happened to be numerically less than t2, then the 7.1
definition seems to silently assume that t2 refers to the time on the
day earlier to t1. This is what conversion of the right operand to
interval and then normalising effectively means - an assumption about
which day the right operand refers to. OTOH the new definition
effectively assumes that both times always refer to the same day.
While the earlier definition seems more natural to me in the context of
my current application (find the duration of the journey from a travel
time-table) , I concede that the new definition might appear to be
logical in other applications.

> 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.

This seems too drastic a solution since my application runs on a shared
server and I am loath to force my convention on others who share the
postmaster.

I wonder if it is possible for me to refine the "-" operator for
operands of type time, only for my application if I do not have
superuser privileges.? If this can be done, then I would be happy to
take that route and avoid modifying the sources.

> 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.

Same here and thanks once again for the help. After both my earlier
posts failed to get a response, I was beginning to wonder if I was the
only one to do something as silly as subtract times rather than subtract
timestamps ;)

Regards,
Gurunandan