From: Daniel Gustafsson <daniel@yesql.se>
Message-Id: <1B595919-2596-4CB3-BC39-DDCCB9876E56@yesql.se>
Content-Type: multipart/mixed;
	boundary="Apple-Mail=_9E47FC16-330D-4840-9FC4-EEE4E248FA33"
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3776.700.51.11.2\))
Subject: Re: to_date()/to_timestamp() silently accept month=0 and day=0
Date: Fri, 24 Apr 2026 14:44:04 +0200
In-Reply-To: <CAJTYsWVEk5vdy8of2DCWzDNJgHUNL0p0Dt6b1zkgmvmNo3ht6w@mail.gmail.com>
Cc: Michael Paquier <michael@paquier.xyz>,
 Tom Lane <tgl@sss.pgh.pa.us>,
 pgsql-bugs@lists.postgresql.org
To: Ayush Tiwari <ayushtiwari.slg01@gmail.com>
References: <CAJTYsWWNfF+hMpbsTDF8NMr+AuqoDfNxR=oHUZm7xqGP+dJ9rA@mail.gmail.com>
 <aenQ37wBgKqZb7a8@paquier.xyz>
 <FE1D12FF-7E5D-4ED0-A59F-BEA6DEF7EE2C@yesql.se>
 <1444609.1776958814@sss.pgh.pa.us>
 <CAJTYsWUJFHhqiGD+6vw6m0ELqjajoKev2UutFUMB2uQDjm6+TA@mail.gmail.com>
 <aeqZukF8X29eWtqy@paquier.xyz>
 <B96867CD-3533-40BA-AE09-43DB2CA5DC48@yesql.se>
 <CAJTYsWV1wEuv2ZbBHt4BQ2O4-Na6JdxSU3b--GFzs-j5cG_pVA@mail.gmail.com>
 <CAJTYsWVEk5vdy8of2DCWzDNJgHUNL0p0Dt6b1zkgmvmNo3ht6w@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/1B595919-2596-4CB3-BC39-DDCCB9876E56%40yesql.se>
Precedence: bulk


--Apple-Mail=_9E47FC16-330D-4840-9FC4-EEE4E248FA33
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=us-ascii

> On 24 Apr 2026, at 11:13, Ayush Tiwari <ayushtiwari.slg01@gmail.com> =
wrote:

> Attached patch, please review.

LGTM for the most part, I don't really think we need to use both to_date =
and
to_timestamp though, we can save a few cycles there.  I rewrote the =
comments to
match the rest of the file, and moved to where to where we test for year =
0000
since it seems like a better place.  Also took the liberty to use year =
100 in
one of the testcase, while the year is superfluous for the test in =
question,
year 100 was previously untested so this will increase test coverage for =
free.

--
Daniel Gustafsson


--Apple-Mail=_9E47FC16-330D-4840-9FC4-EEE4E248FA33
Content-Disposition: attachment;
	filename=v2-0001-Add-regression-tests-for-zero-month-day-handling-.patch
Content-Type: application/octet-stream;
	x-unix-mode=0644;
	name="v2-0001-Add-regression-tests-for-zero-month-day-handling-.patch"
Content-Transfer-Encoding: quoted-printable

=46rom=20dc0b7a09e0e02c587ba594d5af741c14186969e1=20Mon=20Sep=2017=20=
00:00:00=202001=0AFrom:=20Daniel=20Gustafsson=20=
<dgustafsson@postgresql.org>=0ADate:=20Fri,=2024=20Apr=202026=2014:37:05=20=
+0200=0ASubject:=20[PATCH=20v2]=20Add=20regression=20tests=20for=20zero=20=
month/day=20handling=20in=0A=20to_date=0A=0AZero=20values=20for=20month=20=
and=20day=20fields=20in=20to_date()=20(and=20to_timestamp())=0Aformat=20=
strings=20are=20intentionally=20treated=20as=20missing=20data=20and=20=
default=0Ato=201=20(January=20/=20day-of-month=201),=20matching=20the=20=
ZERO_tm()=20initialization=0Ain=20formatting.c.=20=20Add=20tests=20=
documenting=20this=20long-standing=20behavior.=0A=0AAuthor:=20Ayush=20=
Tiwari=20<ayushtiwari.slg01@gmail.com>=0ADiscussion:=20=
https://postgr.es/m/CAJTYsWWNfF+hMpbsTDF8NMr+AuqoDfNxR=3DoHUZm7xqGP+dJ9rA@=
mail.gmail.com=0A---=0A=20src/test/regress/expected/horology.out=20|=20=
25=20+++++++++++++++++++++++++=0A=20src/test/regress/sql/horology.sql=20=20=
=20=20=20=20|=20=205=20+++++=0A=202=20files=20changed,=2030=20=
insertions(+)=0A=0Adiff=20--git=20=
a/src/test/regress/expected/horology.out=20=
b/src/test/regress/expected/horology.out=0Aindex=20=
32cf62b6741..6c8f4b1c19d=20100644=0A---=20=
a/src/test/regress/expected/horology.out=0A+++=20=
b/src/test/regress/expected/horology.out=0A@@=20-3818,6=20+3818,31=20@@=20=
SELECT=20to_date('0000-02-01','YYYY-MM-DD');=20=20--=20allowed,=20though=20=
it=20shouldn't=20be=0A=20=2002-01-0001=20BC=0A=20(1=20row)=0A=20=0A+--=20=
Zero=20month/day=20values=20are=20treated=20as=20missing=20data=20and=20=
default=20to=201=0A+SELECT=20to_date('2024-00-15',=20'YYYY-MM-DD');=20=20=
--=20ok,=200=20->=20January=0A+=20=20to_date=20=20=20=0A+------------=0A=
+=2001-15-2024=0A+(1=20row)=0A+=0A+SELECT=20to_date('2024-01-00',=20=
'YYYY-MM-DD');=20=20--=20ok,=200=20->=201'st=0A+=20=20to_date=20=20=20=0A=
+------------=0A+=2001-01-2024=0A+(1=20row)=0A+=0A+SELECT=20=
to_date('100-00-00',=20'YYY-MM-DD');=20=20--=20ok,=200/0=20->=20Jan=20=
1st,=20year=20100=20->=202100=0A+=20=20to_date=20=20=20=0A+------------=0A=
+=2001-01-2100=0A+(1=20row)=0A+=0A+SELECT=20to_date('100',=20'YYY');=20=
--=20ok,=20equivalent=20to=20above=0A+=20=20to_date=20=20=20=0A=
+------------=0A+=2001-01-2100=0A+(1=20row)=0A+=0A=20SELECT=20=
to_date('100000000',=20'CC');=0A=20ERROR:=20=20date/time=20field=20value=20=
out=20of=20range:=20"100000000"=0A=20SELECT=20to_date('-100000000',=20=
'CC');=0Adiff=20--git=20a/src/test/regress/sql/horology.sql=20=
b/src/test/regress/sql/horology.sql=0Aindex=208978249a5dc..ec4ef6814a5=20=
100644=0A---=20a/src/test/regress/sql/horology.sql=0A+++=20=
b/src/test/regress/sql/horology.sql=0A@@=20-670,6=20+670,11=20@@=20=
SELECT=20to_date('2016=20365',=20'YYYY=20DDD');=20=20--=20ok=0A=20SELECT=20=
to_date('2016=20366',=20'YYYY=20DDD');=20=20--=20ok=0A=20SELECT=20=
to_date('2016=20367',=20'YYYY=20DDD');=0A=20SELECT=20=
to_date('0000-02-01','YYYY-MM-DD');=20=20--=20allowed,=20though=20it=20=
shouldn't=20be=0A+--=20Zero=20month/day=20values=20are=20treated=20as=20=
missing=20data=20and=20default=20to=201=0A+SELECT=20=
to_date('2024-00-15',=20'YYYY-MM-DD');=20=20--=20ok,=200=20->=20January=0A=
+SELECT=20to_date('2024-01-00',=20'YYYY-MM-DD');=20=20--=20ok,=200=20->=20=
1'st=0A+SELECT=20to_date('100-00-00',=20'YYY-MM-DD');=20=20--=20ok,=20=
0/0=20->=20Jan=201st,=20year=20100=20->=202100=0A+SELECT=20=
to_date('100',=20'YYY');=20--=20ok,=20equivalent=20to=20above=0A=20=
SELECT=20to_date('100000000',=20'CC');=0A=20SELECT=20=
to_date('-100000000',=20'CC');=0A=20SELECT=20to_date('-2147483648=2001',=20=
'CC=20YY');=0A--=20=0A2.39.3=20(Apple=20Git-146)=0A=0A=

--Apple-Mail=_9E47FC16-330D-4840-9FC4-EEE4E248FA33--