Hi,
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.
For example:
SET TimeZone = 'America/New_York';
SELECT
'2020-03-08 02:30'::timestamp < '2020-03-08 03:00'::timestamp,
'2020-03-08 02:30'::timestamp > '2020-03-08 03:00-04'::timestamptz,
'2020-03-08 03:00'::timestamp = '2020-03-08 03:00-04'::timestamptz;
This returns true, true, true: 02:30 precedes 03:00 in timestamp order, but
compares after a timestamptz value that the later 03:00 equals.
This can e.g. lead to incorrect results when we query by an index:
CREATE TABLE t (x timestamp);
INSERT INTO t VALUES ('2020-03-08 02:30'), ('2020-03-08 03:00');
CREATE INDEX ON t (x);
SET enable_seqscan = off;
SELECT * FROM t WHERE x = '2020-03-08 03:00-04'::timestamptz;
-- 0 rows
SET enable_indexscan = off;
SET enable_bitmapscan = off;
SELECT * FROM t WHERE x = '2020-03-08 03:00-04'::timestamptz;
-- 2020-03-08 03:00:00
The index scan stops after comparing 02:30 greater than the search key, and
never reaches the later matching 03:00 entry. The same ordering problem
also affects merge joins and partition pruning.
This seems maybe annoying to fix, but the alternative could be adding some
documentation about it?
[0] https://www.postgresql.org/message-id/795934.1626980947%40sss.pgh.pa.us
Jacob
