BUG #19053: Inconsistent arithmetic regarding TIMESTAMPTZ and INTERVAL

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: jbe-mlist(at)magnetkern(dot)de
Subject: BUG #19053: Inconsistent arithmetic regarding TIMESTAMPTZ and INTERVAL
Date: 2025-09-15 10:12:58
Message-ID: 19053-669df0ccf3d14d52@postgresql.org
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 19053
Logged by: Jan Behrens
Email address: jbe-mlist(at)magnetkern(dot)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.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Robert Haas 2025-09-15 14:46:45 Re: BUG #18959: Name collisions of expression indexes during parallel Index creations on a pratitioned table.
Previous Message PG Bug reporting form 2025-09-15 09:10:18 BUG #19052: failing to install on Windows if user path contains Chineese/Korean characters