timestamp vs. timestamptz comparisons inconsistently ordered under DST

On Sun, 2026-07-26 at 18:39 -0700, Jacob Brazeal wrote:

I found an interesting exception to the principle here [0] that timestamp vs. timestamptz
comparisons of the datetime_ops btree family are always consistently ordered: a DST
spring-forward gap.

[...]

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.

Since the problem is the implicit cast from timestamp to timestamptz,
an index scan would be fine for certain values of the "timezone" parameter,
but we cannot know the setting at query planning time.

I'd say that the fix is not to attempt an index scan in such a situation,
that is, to remove those operators from the operator family.
I am not sure what to do in the back branches, though.

Yours,
Laurenz Albe

Laurenz Albe <laurenz.albe@cybertec.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

On Mon, 2026-07-27 at 08:23 -0400, Tom Lane wrote:

Laurenz Albe <laurenz.albe@cybertec.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.

You are right.

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.

What if we change the definition of the SQL operators accordingly?
That should only affect very few actual values. We can do that
in master, with a note in the compatibility section, right?

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

That doesn't look attractive at all.

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.

I agree with your assessment of the happiness:unhappiness ratio.

But wouldn't that also mean that very few people are going to be
unhappy if we backpatch a change in the behavior of the SQL
operators?

Yours,
Laurenz Albe

Laurenz Albe <laurenz.albe@cybertec.at> writes:

On Mon, 2026-07-27 at 08:23 -0400, Tom Lane wrote:

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.

What if we change the definition of the SQL operators accordingly?
That should only affect very few actual values. We can do that
in master, with a note in the compatibility section, right?

It would behave very strangely I think: you'd have a situation where,
eg,
A::timestamp > B::timestamptz
B::timestamptz < A::timestamp
sometimes give different answers, so that we'd have to stop marking
them as commutators. The downsides of that seem pretty awful.

But wouldn't that also mean that very few people are going to be
unhappy if we backpatch a change in the behavior of the SQL
operators?

Backpatching a behavioral change like this seems awfully scary.
For the moment I'm just contemplating what we could potentially
change in master. So far I don't like any of the choices :-(

regards, tom lane

On Mon, 2026-07-27 at 13:06 -0400, Tom Lane wrote:

Laurenz Albe <laurenz.albe@cybertec.at> writes:

On Mon, 2026-07-27 at 08:23 -0400, Tom Lane wrote:

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.

What if we change the definition of the SQL operators accordingly?
That should only affect very few actual values.  We can do that
in master, with a note in the compatibility section, right?

It would behave very strangely I think: you'd have a situation where,
eg,
A::timestamp > B::timestamptz
B::timestamptz < A::timestamp
sometimes give different answers, so that we'd have to stop marking
them as commutators.  The downsides of that seem pretty awful.

I thought about that for a while, but I can't see how it could happen.
If the "timestamptz" side is cast to "timestamp", it would be cast to
the same value in both cases. So that would mean that there are two
"timestamp" values where x > y is different from y < x.
Where is the hole in my reasoning?

Yours,
Laurenz Albe

Laurenz Albe <laurenz.albe@cybertec.at> writes:

On Mon, 2026-07-27 at 13:06 -0400, Tom Lane wrote:

It would behave very strangely I think: you'd have a situation where,
eg,
A::timestamp > B::timestamptz
B::timestamptz < A::timestamp
sometimes give different answers, so that we'd have to stop marking
them as commutators.  The downsides of that seem pretty awful.

I thought about that for a while, but I can't see how it could happen.
If the "timestamptz" side is cast to "timestamp", it would be cast to
the same value in both cases. So that would mean that there are two
"timestamp" values where x > y is different from y < x.

AFAICS, what we'd need to pursue this route is that:

1. timestamptz_cmp_timestamp, which is used for an index on
timestamptz with a query value of type timestamp, would have to
promote the timestamp to timestamptz and then compare (same as
it ever was). If you instead convert the timestamptz side then
you have the exact same problem that's being complained of,
though probably for different data values: the index sort
ordering looks inconsistent.

2. timestamp_cmp_timestamptz, which is used for an index on
timestamp with a query value of type timestamptz, would have to
convert the timestamptz side to timestamp and then compare.

If we sync the SQL operators with that, then what we have is a
situation where timestamptz-on-the-left cases are inconsistent
with timestamptz-on-the-right cases. I don't see that people
will find that intuitive.

regards, tom lane

On Mon, 2026-07-27 at 13:06 -0400, Tom Lane wrote:

So far I don't like any of the choices :-(

If nobody can think of a smart solution, should I try to add an
(embarrassing) warning note to the documentation, or do we simply
sweep it under the carpet?

Yours,
Laurenz Albe