pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #19053: Inconsistent arithmetic regarding TIMESTAMPTZ and INTERVAL
4+ messages / 4 participants
[nested] [flat]
* BUG #19053: Inconsistent arithmetic regarding TIMESTAMPTZ and INTERVAL
@ 2025-09-15 10:12 PG Bug reporting form <noreply@postgresql.org>
2025-09-15 15:41 ` Re: BUG #19053: Inconsistent arithmetic regarding TIMESTAMPTZ and INTERVAL Tom Lane <tgl@sss.pgh.pa.us>
2025-09-15 16:03 ` Re:BUG #19053: Inconsistent arithmetic regarding TIMESTAMPTZ and INTERVAL ocean_li_996 <ocean_li_996@163.com>
0 siblings, 2 replies; 4+ messages in thread
From: PG Bug reporting form @ 2025-09-15 10:12 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: jbe-mlist@magnetkern.de
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19053: Inconsistent arithmetic regarding TIMESTAMPTZ and INTERVAL
2025-09-15 10:12 BUG #19053: Inconsistent arithmetic regarding TIMESTAMPTZ and INTERVAL PG Bug reporting form <noreply@postgresql.org>
@ 2025-09-15 15:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-09-15 17:24 ` Re: BUG #19053: Inconsistent arithmetic regarding TIMESTAMPTZ and INTERVAL Jan Behrens <jbe-mlist@magnetkern.de>
1 sibling, 1 reply; 4+ messages in thread
From: Tom Lane @ 2025-09-15 15:41 UTC (permalink / raw)
To: jbe-mlist@magnetkern.de; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19053: Inconsistent arithmetic regarding TIMESTAMPTZ and INTERVAL
2025-09-15 10:12 BUG #19053: Inconsistent arithmetic regarding TIMESTAMPTZ and INTERVAL PG Bug reporting form <noreply@postgresql.org>
2025-09-15 15:41 ` Re: BUG #19053: Inconsistent arithmetic regarding TIMESTAMPTZ and INTERVAL Tom Lane <tgl@sss.pgh.pa.us>
@ 2025-09-15 17:24 ` Jan Behrens <jbe-mlist@magnetkern.de>
0 siblings, 0 replies; 4+ messages in thread
From: Jan Behrens @ 2025-09-15 17:24 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org
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 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.
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re:BUG #19053: Inconsistent arithmetic regarding TIMESTAMPTZ and INTERVAL
2025-09-15 10:12 BUG #19053: Inconsistent arithmetic regarding TIMESTAMPTZ and INTERVAL PG Bug reporting form <noreply@postgresql.org>
@ 2025-09-15 16:03 ` ocean_li_996 <ocean_li_996@163.com>
1 sibling, 0 replies; 4+ messages in thread
From: ocean_li_996 @ 2025-09-15 16:03 UTC (permalink / raw)
To: jbe-mlist@magnetkern.de <jbe-mlist@magnetkern.de>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2025-09-15 17:24 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-09-15 10:12 BUG #19053: Inconsistent arithmetic regarding TIMESTAMPTZ and INTERVAL PG Bug reporting form <noreply@postgresql.org>
2025-09-15 15:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-09-15 17:24 ` Jan Behrens <jbe-mlist@magnetkern.de>
2025-09-15 16:03 ` ocean_li_996 <ocean_li_996@163.com>
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