The following bug has been logged on the website:
Bug reference: 18313
Logged by: Christian Maurer
Email address: c.maurer@gmx.at
PostgreSQL version: 16.1
Operating system: RHEL 9
Description:
Hi
The SQL script below uses an anchor date (2000-01-01) in a table and tries
to subtract an interval (in days) from it.
Considering the timestamp range, the maximum number of days that can be
successfully subtracted should be 2451545. All higher values are then
expected to trigger an exception.
However, when subtracting 2483590, no error is triggered. This is true up to
2539738.
Subtracting 2539739 again shows an error message.
Is this the intended behavior?
Platform: We use PostgreSQL 16.1 (PGDG) under Red Hat Enterprise Linux 9
with standard settings.
Regards,
Christian
create table tbl_timestamp_limit (anchor_date TIMESTAMP(6), offset_value
BIGINT);
insert into tbl_timestamp_limit (anchor_date) values
(to_timestamp('20000101','YYYYMMDD'));
-- the maximum value in days we can subtract from '2000-01-01' without an
error is 2451545
update tbl_timestamp_limit set offset_value=2483589;
-- 2483589 fails as expected
SELECT (anchor_date - INTERVAL '1 day' * (offset_value)) FROM
tbl_timestamp_limit;
-- one day more
update tbl_timestamp_limit set offset_value=2483590;
-- 2483590 surprisingly succeeds, no error is triggered
SELECT (anchor_date - INTERVAL '1 day' * (offset_value)) FROM
tbl_timestamp_limit;
update tbl_timestamp_limit set offset_value=2539738;
-- 2539738 still does not trigger an error
SELECT (anchor_date - INTERVAL '1 day' * (offset_value)) FROM
tbl_timestamp_limit;
update tbl_timestamp_limit set offset_value=2539739;
-- 2539739 fails as expected
SELECT (anchor_date - INTERVAL '1 day' * (offset_value)) FROM
tbl_timestamp_limit;