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
On Fri, May 25, 2018 at 3:33 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
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.
https://www.postgresql.org/docs/10/static/datatype-datetime.html
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