BUG #19053: Inconsistent arithmetic regarding TIMESTAMPTZ and INTERVAL
От | PG Bug reporting form |
---|---|
Тема | BUG #19053: Inconsistent arithmetic regarding TIMESTAMPTZ and INTERVAL |
Дата | |
Msg-id | 19053-669df0ccf3d14d52@postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #19053: Inconsistent arithmetic regarding TIMESTAMPTZ and INTERVAL
Re:BUG #19053: Inconsistent arithmetic regarding TIMESTAMPTZ and INTERVAL |
Список | pgsql-bugs |
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.
В списке pgsql-bugs по дате отправления: