Re: Timetz comparison

"David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> On Fri, May 25, 2018 at 3:33 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Even if you'd made a case why we should consider them equal,
>> those would be very good reasons not to change behavior that's
>> stood for 17 years.

> This is true, and the alternative doesn't have the supporting argument of
> being spec-compliant either...

I poked around in the standard to see what it has to say on the subject.
SQL:2011 section 8.2 <comparison predicate> general rule 6 says "the
comparison of two datetimes is determined according to the interval
resulting from their subtraction". That's promising, except we don't
actually implement timetz subtraction:

# select '22:00+02'::timetz - '23:00+01'::timetz;
ERROR: operator does not exist: time with time zone - time with time zone

But surely the spec defines it ... digging around, it's in 6.33 <interval
value expression> general rule 7. That describes rotating both values to
the same time zone and then subtracting, which would seem to provide some
ammunition for Alexey's point of view. But then they throw it all away:

The difference of two values of type TIME (with or without time zone)
is constrained to be between –24:00:00 and +24:00:00 (excluding each
end point); it is implementation-defined which of two non-zero values
in this range is the result, although the computation shall be
deterministic.

In other words, the implementation is actually free to choose the sign
of the subtraction result, which means that the spec fails to define
the result of timetz comparison: all that's required is that it be
consistent with your implementation of timetz subtraction. Since we
don't have the latter (and I don't recall anyone asking for it...)
there's not much to argue from here.

> The notes in 8.5.3 Time Zone (v10 docs) seem to apply here overall - the
> type, while standard, is ill-conceived.

Yeah, this. There are a *lot* of weirdnesses in the spec's treatment of
datetimes, and specifically their notion of timezones just has darn little
to do with anyone's reality. So in general I'm not that excited about
getting closer to spec in this area.

regards, tom lane