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