BUG #19053: Inconsistent arithmetic regarding TIMESTAMPTZ and INTERVAL
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.
PG Bug reporting form <noreply@postgresql.org> writes:
Consider the following calculations:
AFAICS all of these are behaving as-expected. Yeah, it's confusing,
but expecting calendar calculations to have mathematical rigor is
a fool's errand. The intent of what's implemented is to produce
useful results for calculations like
regression=# SELECT TIMESTAMPTZ '2025-03-30' + INTERVAL '1 day';
?column?
------------------------
2025-03-31 00:00:00+02
(1 row)
despite the intervening DST change.
regards, tom lane
Hello,
I think this is caused by the daylight saving time and standard time changes in the Europe/Berlin timezone.
It is not a bug, but the expected behavior. In 2025, daylight saving time starts at '2025-03-30 01:00 UTC'
and ends at '2025-10-26 01:00 UTC'. When changing, it will make one hour gap.
--
regards
Haiyang Li
On Mon, 15 Sep 2025 11:41:29 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:
AFAICS all of these are behaving as-expected.
Well, I would say each calculation by itself can be somewhat explained.
But it's the overall combination of behavior that seems inconsistent.
In particular:
TIMESTAMPTZ '2025-10-27' - TIMESTAMPTZ '2025-10-26'
This gives INTERVAL '1 day 01:00:00', which makes sense in some way.
However, knowing that '24 hours' are not the same as '1 day', I would
rather expect either '25 hours' or '1 day'.
The following does actually make sense:
TIMESTAMPTZ '2025-10-26' + INTERVAL '1 day 01:00:00'
But it's the previous substraction that, given how INTERVAL behaves
here, does not make sense.
Yeah, it's confusing,
but expecting calendar calculations to have mathematical rigor is
a fool's errand.
I don't expect mathematical rigor, but I would like to have consistent
semantics of what INTERVAL '1 day' means, and when it is used and when
it is returned.
The intent of what's implemented is to produce
useful results for calculations likeregression=# SELECT TIMESTAMPTZ '2025-03-30' + INTERVAL '1 day';
?column?
------------------------
2025-03-31 00:00:00+02
(1 row)despite the intervening DST change.
Yes, I agree that the addition behaves correctly. I just wonder if the
substraction would need to return either '1 day' or '25 hours'. But not
'1 day 01:00:00'.
regards, tom lane
Regards,
Jan Behrens