BUG #19650: to_date()/to_timestamp() silently truncate 4+-digit day-of-year input to the first three digits

чт, 3 сент. 2026 г. в 18:02, PG Bug reporting form <noreply@postgresql.org>:

The following bug has been logged on the website:

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

The documentation states that DDD (day of year) accepts values 001-366 and
IDDD (ISO day of year) 001-371, and out-of-range values raise an error.
That
check works for 3-digit input, but for input of 4 or more digits the parser
silently keeps only the first three digits and treats them as a valid day
number:

SELECT to_date('2024 1000', 'YYYY DDDD');
-- 2024-04-09 (parsed as day 100 — the first three digits)

SELECT to_date('2024 1234', 'YYYY DDDD');
-- 2024-05-02 (parsed as day 123)

SELECT to_date('2024 10000', 'YYYY DDDD');
-- 2024-04-09 (5-digit input, still day 100)

SELECT to_date('2024 999', 'IYYY IDDD');
-- 2026-09-25 (IDDD 999 silently accepted; an ISO year has at most 371
days)

```
hunt@(null)=# SELECT to_date('2024 1000', 'YYYY DDDD');
-- 2024-04-09 (parsed as day 100 — the first three digits)

SELECT to_date('2024 1234', 'YYYY DDDD');
-- 2024-05-02 (parsed as day 123)

SELECT to_date('2024 10000', 'YYYY DDDD');
-- 2024-04-09 (5-digit input, still day 100)

SELECT to_date('2024 999', 'IYYY IDDD');
-- 2026-09-25 (IDDD 999 silently accepted; an ISO year has at most 371
days)
to_date
------------
2024-04-09
(1 row)

to_date
------------
2024-05-02
(1 row)

to_date
------------
2024-04-09
(1 row)

to_date
------------
2026-09-25
(1 row)

hunt@(null)=# SELECT to_date('2024 367', 'YYYY DDD');
-- ERROR: date/time field value out of range: "2024 367"
ERROR: date/time field value out of range: "2024 367"
```

Hi!

Thanks for the report.

The holes are:

DDD / IDDD 001-366 / 001-371
SSSSS 0-86399
RM I-XII
IW 01-53 (WW 54+ already errored before; now checked
uniformly)
ID 1-7

YYYY DDDD parses as YYYY + DDD + weekday D. D is accepted but
ignored for date computation, so to_date('2024 1000', 'YYYY DDDD')
is not a DDD overflow case. to_date('2024 1000', 'YYYY DDD')
already failed, and still does.

The checks run when the field is parsed. A later 0 in TmFromChar
means the field was unset, so ID 0 and IW 0 cannot be rejected in
do_to_timestamp. SSSSS recovers a minus that was swallowed as a
separator, the same way TZH does. RM rejects a leftover roman
digit so XIII is not taken as XII.

Patch with regress in attachment.

--
Regards,
Rachitskiy Andrey

Hi Andrey,

I tested the patch locally against current master (20devel).
The original out-of-range cases are rejected with the patch applied. I
also tested the boundaries and a few related cases.

For SSSSS, the patch fixes the negative-value case while preserving the
existing valid boundaries:
```
SSSSS = -1 ERROR
SSSSS = 0 accepted
SSSSS = 86399 accepted
SSSSS = 86400 ERROR
```
In particular, on unpatched master
```
to_timestamp('2024-01-01 -1', 'YYYY-MM-DD SSSSS')
```
returns 00:00:01, while with the patch it correctly reports an
out-of-range error.
I also checked the other fields covered by the patch:
```
RM: XII accepted, XIII rejected
IW: 00 rejected, 01..53 accepted, 54 rejected
ID: 0 rejected, 1..7 accepted, 8 rejected
DDD: 001..366 is still subject to the existing year-aware check
IDDD: 001..371 accepted, 372 rejected
```
I checked some ISO boundary combinations as well. The patch retains the
existing normalization behavior for values that are within the field's
documented range. For example, IW 53 in an ISO year that does not
actually have week 53 rolls into the following ISO year:
```
2019 53 1 -> 2019-12-30 -> 2020-01-1
2020 53 1 -> 2020-12-28 -> 2020-53-1
2024 53 1 -> 2024-12-30 -> 2025-01-1
2026 53 1 -> 2026-12-28 -> 2026-53-1
```
Similarly,
```
to_date('2024 371', 'IYYY IDDD')
```
returns 2025-01-05. So the new checks appear to be limited to the
documented ranges for the ISO fields and do not otherwise change their
normalization semantics.

I also verified the DDDD case discussed earlier. Inputs such as
```
to_date('2024 1000', 'YYYY DDDD')
to_date('2024 1234', 'YYYY DDDD')
```
remain accepted, consistent with DDDD being parsed as DDD followed by D.

The regression tests pass with the patch applied.

Best Regards!
Clemenza Zhang