Re: timestamp vs. timestamptz comparisons inconsistently ordered under DST

Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> writes:
> On Sun, 2026-07-26 at 18:39 -0700, Jacob Brazeal wrote:
>> This seems maybe annoying to fix, but the alternative could be adding some documentation about it?

> It is quite unacceptable to allow a bad query result, so documenting
> this is not good enough in my opinion.

It's not really the index's fault IMO: the locus of the problem is
inconsistent promotions of timestamp to timestamptz. Observe:

regression=# select '2020-03-08 01:59'::timestamp::timestamptz;
timestamptz
------------------------
2020-03-08 01:59:00-05
(1 row)

regression=# select '2020-03-08 02:00'::timestamp::timestamptz;
timestamptz
------------------------
2020-03-08 03:00:00-04
(1 row)

regression=# select '2020-03-08 02:30'::timestamp::timestamptz;
timestamptz
------------------------
2020-03-08 03:30:00-04
(1 row)

regression=# select '2020-03-08 03:00'::timestamp::timestamptz;
timestamptz
------------------------
2020-03-08 03:00:00-04 <--- less than the previous result
(1 row)

Our general rule for timestamp-vs-timestamptz comparisons is
"promote the timestamp side to timestamptz, then compare".
So we get the effect Jacob describes that an index that is
validly ordered per timestamp rules appears out-of-order
when it's being compared to a timestamptz query value.

I thought for a bit about changing the support function
timestamp_cmp_timestamptz so that it instead converts the
timestamptz side down to timestamp. That would make an index
search work consistently ... but it would be inconsistent with
what the SQL-accessible operators do, so I think it's a nonstarter.

I think the only way we could really resolve this is to change
the promotion rule to be monotonic, that is "all timestamp values
between 02:00 and 03:00 convert to 03:00". But that probably
breaks other things, and in abstract terms it doesn't seem
better than what we do now (which we define as "assume that
times in the gap are meant to be standard time").

Or, as you say, we could stop treating timestamp-vs-timestamptz as an
index-scannable query. But the people that that would make unhappy
(because their query suddenly takes forever) surely vastly outnumber
the people whom it'd make happy (approximately no one, given the lack
of prior complaints). It's not an issue unless you store timestamp
values that don't actually correspond to any local clock reading.

regards, tom lane