From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: jbe-mlist@magnetkern.de
Subject: BUG #19053: Inconsistent arithmetic regarding TIMESTAMPTZ and INTERVAL
Date: Mon, 15 Sep 2025 10:12:58 +0000
Message-ID: <19053-669df0ccf3d14d52@postgresql.org> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, jbe-mlist@magnetkern.de
Subject: Re: BUG #19053: Inconsistent arithmetic regarding TIMESTAMPTZ and INTERVAL
In-Reply-To: <19053-669df0ccf3d14d52@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox