Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Subject: BUG #19651: to_date()/to_timestamp() silently accept out-of-range
 values for SSSSS, RM, IW and ID format fields
To: pgsql-bugs@lists.postgresql.org
From: PG Bug reporting form <noreply@postgresql.org>
Cc: 303677365@qq.com
Reply-To: 303677365@qq.com, pgsql-bugs@lists.postgresql.org
Date: Thu, 03 Sep 2026 06:46:30 +0000
Message-ID: <19651-fdc8457e83a39ba1@postgresql.org>
Auto-Submitted: auto-generated
Archived-At: 
 <https://www.postgresql.org/message-id/19651-fdc8457e83a39ba1%40postgresql.org>
Precedence: bulk

The following bug has been logged on the website:

Bug reference:      19651
Logged by:          chunling qin
Email address:      303677365@qq.com
PostgreSQL version: 18.6
Operating system:   x86_64
Description:       =20

Four to_date()/to_timestamp() format fields whose documented value ranges
are narrow accept out-of-range input silently and produce a wrong date,
while sister fields in the same domain raise errors for the same kind of
input:

1. SSSSS (seconds since midnight, documented 0-86399) =E2=80=94 negative va=
lue
silently negated:

SELECT to_timestamp('2024-01-01 -1', 'YYYY-MM-DD SSSSS');
-- 2024-01-01 00:00:01+00      (-1 second becomes +1 second)
2. RM (Roman month numeral, documented I..XII) =E2=80=94 out-of-range numer=
al
accepted:

SELECT to_date('2024 XIII', 'YYYY RM');
-- 2024-12-01                 (XIII parsed as month 12)
3. IW (ISO week, documented 01-53) =E2=80=94 values 54-99 accepted, silently
spilling into the following year. The sister field WW validates the same
value correctly:

SELECT to_date('2024 54', 'IYYY IW');
-- 2025-01-06                 (no error)
SELECT to_date('2024 99', 'IYYY IW');
-- 2025-11-17                 (no error)

SELECT to_date('2024 54', 'YYYY WW');
-- ERROR:  date/time field value out of range: "2024 54"
4. ID (ISO day of week, documented 1-7) =E2=80=94 both 0 and 8 accepted, ea=
ch mapped
to Sunday:

SELECT to_date('2024 01 8', 'IYYY IW ID');
-- 2024-01-07                 (should be an error)
SELECT to_date('2024 01 0', 'IYYY IW ID');
-- 2024-01-07                 (same Sunday result as 7)
Expected behavior
Each field should raise date/time field value out of range for input outside
its documented domain, consistently with how DDD (367+), WW (54+), MM (13+),
DD (32+) and others already behave.