Thread: Timetz comparison
Hello, Comparison of timetz values looks a bit weird to me, as '22:00+02'::timetz > '21:00+01'::timetz. I can see this behavior introduced by commit 2792374c in 2001 with the comment "timetz was just plain broken (some possible pairs of values were neither < nor = nor >)". The in-code comment is: /* * If same GMT time, sort by timezone; we only want to say that two * timetz's are equal if both the time and zone parts are equal. */ However I found no specification on how timetz values are compared neither in postgres docs nor in the standard (http://www.wiscorp.com/sql20nn.zip). Was this decision made just to be definite? What's the problem with these values to be considered equal? Backward compatibility? Hash algorithms? Thanks Best regards, Alexey
Alexey Bashtanov <bashtanov@imap.cc> writes:
> Comparison of timetz values looks a bit weird to me, as
> '22:00+02'::timetz > '21:00+01'::timetz.
Perhaps, but I don't think there's a reasonable case for considering
them equal, either. In the other places where obviously-different
values compare equal, such as zero versus minus zero in IEEE floats,
it's widely understood as a gotcha.
> What's the problem with these values to be considered equal?
> Backward compatibility? Hash algorithms?
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.
regards, tom lane
Alexey Bashtanov <bashtanov@imap.cc> writes:
> Comparison of timetz values looks a bit weird to me, as
> '22:00+02'::timetz > '21:00+01'::timetz.
Perhaps, but I don't think there's a reasonable case for considering
them equal, either. In the other places where obviously-different
values compare equal, such as zero versus minus zero in IEEE floats,
it's widely understood as a gotcha.
Except all we've done here is expose an implementation detail since timestamptz compares on a logical "point in time" notion of equality while timetz does not. Limitations of timetz aside adding a random date and changing the type to "timestamptz" would not obviously cause the result of the above comparison to change and the fact that it does could be considered a gotcha when the behavior of timestamptz is likely to be the widely understood and accepted and the behavior of timetz inferred from it.
> What's the problem with these values to be considered equal?
> Backward compatibility? Hash algorithms?
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...
The notes in 8.5.3 Time Zone (v10 docs) seem to apply here overall - the type, while standard, is ill-conceived. Those who cannot stop using it would not appreciate us changing it and those dealing with the oddity now should just use timestamptz.
David J.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Fri, May 25, 2018 at 3:33 PM, Tom Lane <tgl@sss.pgh.pa.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