MIME-Version: 1.0
From: Ayush Tiwari <ayushtiwari.slg01@gmail.com>
Date: Wed, 22 Apr 2026 19:48:00 +0530
Message-ID: <CAJTYsWWNfF+hMpbsTDF8NMr+AuqoDfNxR=oHUZm7xqGP+dJ9rA@mail.gmail.com>
Subject: to_date()/to_timestamp() silently accept month=0 and day=0
To: pgsql-bugs@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000c296de06500d33c1"
Archived-At: <https://www.postgresql.org/message-id/CAJTYsWWNfF%2BhMpbsTDF8NMr%2BAuqoDfNxR%3DoHUZm7xqGP%2BdJ9rA%40mail.gmail.com>
Precedence: bulk

--000000000000c296de06500d33c1
Content-Type: text/plain; charset="UTF-8"

Hi,

I found what looks like a bug in to_date() / to_timestamp(). (Saw it in
master branch)

Inputs with month = 00 or day = 00 are accepted silently and normalized to
January / day 1, instead of being rejected as out of range.

Simple repro steps:

SELECT to_date('2024-00-15', 'YYYY-MM-DD');
SELECT to_date('2024-01-00', 'YYYY-MM-DD');
SELECT to_timestamp('2024-00-15', 'YYYY-MM-DD');
SELECT to_timestamp('2024-01-00', 'YYYY-MM-DD');

Observed results here:

  to_date('2024-00-15', 'YYYY-MM-DD')      -> 2024-01-15
  to_date('2024-01-00', 'YYYY-MM-DD')      -> 2024-01-01
  to_timestamp('2024-00-15', 'YYYY-MM-DD') -> 2024-01-15 00:00:00+05:30
  to_timestamp('2024-01-00', 'YYYY-MM-DD') -> 2024-01-01 00:00:00+05:30

I would expect all four calls to error, similar to how an invalid date
literal
is rejected.

Looking at the root cause in src/backend/utils/adt/formatting.c, the issue
seems
rooted in the interaction between ZERO_tm() and do_to_timestamp().

When to_date()/to_timestamp() initializes pg_tm, it uses ZERO_tm() which
defaults tm->tm_mday = 1 and tm->tm_mon = 1. The TmFromChar struct (tmfc),
which is used to collect the parsed inputs, stores these fields as plain
integers without independent presence flags (has_mm or has_dd have
booleans).

In do_to_timestamp(), the parsed values are applied back into the pg_tm
struct
using simple truthiness checks:

    if (tmfc.mm) tm->tm_mon = tmfc.mm;
    if (tmfc.dd) tm->tm_mday = tmfc.dd;

Because an explicitly parsed 00 evaluates to false, the zeros are never
copied
into pg_tm. Thus, the 1 defaults from ZERO_tm() remain untouched, and the
subsequent validation function ValidateDate() never actually sees the 0 to
throw
an out-of-bounds error.

Fixing this likely requires adding boolean flags to TmFromChar to
distinguish
between an omitted field and an explicitly parsed 0?

Regards,
Ayush

--000000000000c296de06500d33c1
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi,<br><br>I found what looks like a bug in to_date() / to=
_timestamp(). (Saw it in master branch)<br><br>Inputs with month =3D 00 or =
day =3D 00 are accepted silently and normalized to<br>January / day 1, inst=
ead of being rejected as out of range.<br><br>Simple repro steps:<br><br>SE=
LECT to_date(&#39;2024-00-15&#39;, &#39;YYYY-MM-DD&#39;);<br>SELECT to_date=
(&#39;2024-01-00&#39;, &#39;YYYY-MM-DD&#39;);<br>SELECT to_timestamp(&#39;2=
024-00-15&#39;, &#39;YYYY-MM-DD&#39;);<br>SELECT to_timestamp(&#39;2024-01-=
00&#39;, &#39;YYYY-MM-DD&#39;);<br><br>Observed results here:<br><br>=C2=A0=
 to_date(&#39;2024-00-15&#39;, &#39;YYYY-MM-DD&#39;) =C2=A0 =C2=A0 =C2=A0-&=
gt; 2024-01-15<br>=C2=A0 to_date(&#39;2024-01-00&#39;, &#39;YYYY-MM-DD&#39;=
) =C2=A0 =C2=A0 =C2=A0-&gt; 2024-01-01<br>=C2=A0 to_timestamp(&#39;2024-00-=
15&#39;, &#39;YYYY-MM-DD&#39;) -&gt; 2024-01-15 00:00:00+05:30<br>=C2=A0 to=
_timestamp(&#39;2024-01-00&#39;, &#39;YYYY-MM-DD&#39;) -&gt; 2024-01-01 00:=
00:00+05:30<br><br>I would expect all four calls to error, similar to how a=
n invalid date literal<br>is rejected.<br><br>Looking at the root cause in =
src/backend/utils/adt/formatting.c, the issue seems<br>rooted in the intera=
ction between ZERO_tm() and do_to_timestamp().<br><br>When to_date()/to_tim=
estamp() initializes pg_tm, it uses ZERO_tm() which<br>defaults tm-&gt;tm_m=
day =3D 1 and tm-&gt;tm_mon =3D 1. The TmFromChar struct (tmfc),<br>which i=
s used to collect the parsed inputs, stores these fields as plain <br>integ=
ers without independent presence flags (has_mm or has_dd have booleans). <b=
r><br>In do_to_timestamp(), the parsed values are applied back into the pg_=
tm struct <br>using simple truthiness checks:<br><br>=C2=A0 =C2=A0 if (<a h=
ref=3D"http://tmfc.mm">tmfc.mm</a>) tm-&gt;tm_mon =3D <a href=3D"http://tmf=
c.mm">tmfc.mm</a>;<br>=C2=A0 =C2=A0 if (tmfc.dd) tm-&gt;tm_mday =3D tmfc.dd=
;<br><br>Because an explicitly parsed 00 evaluates to false, the zeros are =
never copied<br>into pg_tm. Thus, the 1 defaults from ZERO_tm() remain unto=
uched, and the<br>subsequent validation function ValidateDate() never actua=
lly sees the 0 to throw <br>an out-of-bounds error.<br><br>Fixing this like=
ly requires adding boolean flags to TmFromChar to distinguish <br>between a=
n omitted field and an explicitly parsed 0?<br><br>Regards,<br>Ayush</div>

--000000000000c296de06500d33c1--