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

From: Kirill Reshke <reshkekirill(at)gmail(dot)com>
To: Clemenza Zhang <zxlmgsps2(at)gmail(dot)com>
Cc: Andrey Rachitskiy <pl0h0yp1(at)gmail(dot)com>, 303677365(at)qq(dot)com, pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Re: BUG #19650: to_date()/to_timestamp() silently truncate 4+-digit day-of-year input to the first three digits
Date: 2026-11-20 09:36:15
Message-ID: CALdSSPgY-zu2wKvYE=S7xJSzECq1xCLHCc_nYea_WDswhQzPCQ@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

On Wed, 9 Sept 2026 at 09:32, Clemenza Zhang <zxlmgsps2(at)gmail(dot)com> wrote:
>
> 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
>
> On Thu, Sep 3, 2026 at 11:29 PM Andrey Rachitskiy <pl0h0yp1(at)gmail(dot)com> wrote:
> >
> >
> > чт, 3 сент. 2026 г. в 18:02, PG Bug reporting form <noreply(at)postgresql(dot)org>:
> >>
> >> The following bug has been logged on the website:
> >>
> >> Bug reference: 19650
> >> Logged by: chunling qin
> >> Email address: 303677365(at)qq(dot)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!

I think v1 breaks

SELECT to_date(
to_char(DATE '2024-12-01', 'YYYY FMRM"X"'),
'YYYY FMRM"X"'
);

?
--
Best regards,
Kirill Reshke

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Andrey Rachitskiy 2026-11-20 10:43:41 Re: BUG #19650: to_date()/to_timestamp() silently truncate 4+-digit day-of-year input to the first three digits
Previous Message Andrey Rachitskiy 2026-11-20 06:35:40 Re: BUG #19594: to_char/jsonpath format cache serves a format tree parsed in the wrong strict-mode