The following bug has been logged on the website:
Bug reference: 19053
Logged by: Jan Behrens
Email address: jbe-mlist@magnetkern.de
PostgreSQL version: 17.5
Operating system: FreeBSD
Description:
Consider the following calculations:
jbe=# SET TIME ZONE 'Europe/Berlin';
SET
jbe=# SELECT TIMESTAMPTZ '2025-10-27' - TIMESTAMPTZ '2025-10-26';
?column?
----------------
1 day 01:00:00
(1 row)
jbe=# SELECT TIMESTAMPTZ '2025-10-26' + INTERVAL '1 day 01:00:00'; -- the
following result is surprising, as it is not midnight
?column?
------------------------
2025-10-27 01:00:00+01
(1 row)
jbe=# SELECT TIMESTAMPTZ '2025-03-31' - TIMESTAMPTZ '2025-03-30';
?column?
----------
23:00:00
(1 row)
jbe=# SELECT TIMESTAMPTZ '2025-03-30' + INTERVAL '23:00:00'; -- inconsistent
with the previous addition above, as it is midnight
?column?
------------------------
2025-03-31 00:00:00+02
(1 row)
jbe=# SELECT TIMESTAMPTZ '2025-04-01' - TIMESTAMPTZ '2025-03-30';
?column?
----------------
1 day 23:00:00
(1 row)
jbe=# SELECT TIMESTAMPTZ '2025-03-30' + INTERVAL '1 day 23:00:00'; -- here,
the result isn't midnight again
?column?
------------------------
2025-03-31 23:00:00+02
(1 row)
Or, some of these operations written in a single expression:
jbe=# SELECT TIMESTAMPTZ '2025-03-30' + (TIMESTAMPTZ '2025-03-31' -
TIMESTAMPTZ '2025-03-30');
?column?
------------------------
2025-03-31 00:00:00+02
(1 row)
jbe=# SELECT TIMESTAMPTZ '2025-03-30' + (TIMESTAMPTZ '2025-04-01' -
TIMESTAMPTZ '2025-03-30');
?column?
------------------------
2025-03-31 23:00:00+02
(1 row)
Note that there is no time zone change in between 2025-03-31 and 2025-04-01,
yet the time of the previous two calculations is off by one hour.
