agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedBUG #19651: to_date()/to_timestamp() silently accept out-of-range values for SSSSS, RM, IW and ID format fields
2+ messages / 2 participants
[nested] [flat]
* BUG #19651: to_date()/to_timestamp() silently accept out-of-range values for SSSSS, RM, IW and ID format fields
@ 2026-09-03 06:46 PG Bug reporting form <noreply@postgresql.org>
2026-09-03 15:36 ` Re: BUG #19651: to_date()/to_timestamp() silently accept out-of-range values for SSSSS, RM, IW and ID format fields Andrey Rachitskiy <pl0h0yp1@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2026-09-03 06:46 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: 303677365@qq.com
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:
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) — negative value
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) — out-of-range numeral
accepted:
SELECT to_date('2024 XIII', 'YYYY RM');
-- 2024-12-01 (XIII parsed as month 12)
3. IW (ISO week, documented 01-53) — 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) — both 0 and 8 accepted, each 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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #19651: to_date()/to_timestamp() silently accept out-of-range values for SSSSS, RM, IW and ID format fields
2026-09-03 06:46 BUG #19651: to_date()/to_timestamp() silently accept out-of-range values for SSSSS, RM, IW and ID format fields PG Bug reporting form <noreply@postgresql.org>
@ 2026-09-03 15:36 ` Andrey Rachitskiy <pl0h0yp1@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Andrey Rachitskiy @ 2026-09-03 15:36 UTC (permalink / raw)
To: 303677365@qq.com; pgsql-bugs@lists.postgresql.org
чт, 3 сент. 2026 г. в 18:03, PG Bug reporting form <noreply@postgresql.org>:
> 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:
>
> 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) — negative value
> 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) — out-of-range numeral
> accepted:
>
> SELECT to_date('2024 XIII', 'YYYY RM');
> -- 2024-12-01 (XIII parsed as month 12)
> 3. IW (ISO week, documented 01-53) — 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) — both 0 and 8 accepted, each
> 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.
>
>
>
>
> Hi!
This also pertains to the [0] thread; the patch is in there.
[0]
https://www.postgresql.org/message-id/19650-d7ea430084cdc44a@postgresql.org
--
Regards,
Rachitskiy Andrey
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-09-03 15:36 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-09-03 06:46 BUG #19651: to_date()/to_timestamp() silently accept out-of-range values for SSSSS, RM, IW and ID format fields PG Bug reporting form <noreply@postgresql.org>
2026-09-03 15:36 ` Andrey Rachitskiy <pl0h0yp1@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox