public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ayush Tiwari <[email protected]>
To: Daniel Gustafsson <[email protected]>
Cc: Michael Paquier <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: [email protected]
Subject: Re: to_date()/to_timestamp() silently accept month=0 and day=0
Date: Fri, 24 Apr 2026 14:43:14 +0530
Message-ID: <CAJTYsWVEk5vdy8of2DCWzDNJgHUNL0p0Dt6b1zkgmvmNo3ht6w@mail.gmail.com> (raw)
In-Reply-To: <CAJTYsWV1wEuv2ZbBHt4BQ2O4-Na6JdxSU3b--GFzs-j5cG_pVA@mail.gmail.com>
References: <CAJTYsWWNfF+hMpbsTDF8NMr+AuqoDfNxR=oHUZm7xqGP+dJ9rA@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<CAJTYsWUJFHhqiGD+6vw6m0ELqjajoKev2UutFUMB2uQDjm6+TA@mail.gmail.com>
<[email protected]>
<[email protected]>
<CAJTYsWV1wEuv2ZbBHt4BQ2O4-Na6JdxSU3b--GFzs-j5cG_pVA@mail.gmail.com>
Hi,
On Fri, 24 Apr 2026 at 13:46, Ayush Tiwari <[email protected]>
wrote:
> Hi,
>
> On Fri, 24 Apr 2026 at 13:42, Daniel Gustafsson <[email protected]> wrote:
>
>> > On 24 Apr 2026, at 00:14, Michael Paquier <[email protected]> wrote:
>> >
>> > On Thu, Apr 23, 2026 at 10:06:41PM +0530, Ayush Tiwari wrote:
>> >> If we are not going to change the behaviour, imo, we should
>> >> atleast add a test case to depict this scenario and comment
>> >> that says this is expected behaviour according to the community.
>> >
>> > Yes, we could close the gap with some tests, at least.
>>
>> +1. Do you want to work on this Ayush?
>>
>
> Yes, I'll send a patch with test case today.
>
>
Attached patch, please review.
Regards,
Ayush
Attachments:
[application/octet-stream] 0001-Add-regression-tests-for-to_date-to_timestamp-zero-m.patch (3.5K, 3-0001-Add-regression-tests-for-to_date-to_timestamp-zero-m.patch)
download | inline diff:
From 38d9a1b580f3083ade5f18a42729e1b982ad9a4c Mon Sep 17 00:00:00 2001
From: Ayush Tiwari <[email protected]>
Date: Fri, 24 Apr 2026 14:38:00 +0530
Subject: [PATCH] Add regression tests for to_date()/to_timestamp() zero
month/day handling
Zero values for month and day fields in to_date() and to_timestamp()
format strings are intentionally treated as missing data and default
to 1 (January / day-of-month 1), matching the ZERO_tm() initialization
in formatting.c. Add tests documenting this long-standing behavior.
Reported-by: Ayush Tiwari <[email protected]>
Discussion: https://postgr.es/m/CAJTYsWWNfF+hMpbsTDF8NMr+AuqoDfNxR=oHUZm7xqGP+dJ9rA@mail.gmail.com
---
src/test/regress/expected/horology.out | 33 ++++++++++++++++++++++++++
src/test/regress/sql/horology.sql | 9 +++++++
2 files changed, 42 insertions(+)
diff --git a/src/test/regress/expected/horology.out b/src/test/regress/expected/horology.out
index 32cf62b6741..4bb59e69545 100644
--- a/src/test/regress/expected/horology.out
+++ b/src/test/regress/expected/horology.out
@@ -3778,6 +3778,39 @@ SELECT to_timestamp('613566758', 'W');
ERROR: date/time field value out of range: "613566758"
SELECT to_timestamp('2024 613566758 1', 'YYYY WW D');
ERROR: date/time field value out of range: "2024 613566758 1"
+-- Zero month/day values are treated as missing data and default to 1
+-- (see ZERO_tm() in formatting.c which initializes tm_mon = tm_mday = 1,
+-- and do_to_timestamp() which only overwrites them for non-zero parsed values)
+SELECT to_date('2024-00-15', 'YYYY-MM-DD'); -- month 0 -> defaults to January
+ to_date
+------------
+ 01-15-2024
+(1 row)
+
+SELECT to_date('2024-01-00', 'YYYY-MM-DD'); -- day 0 -> defaults to day 1
+ to_date
+------------
+ 01-01-2024
+(1 row)
+
+SELECT to_date('2024-00-00', 'YYYY-MM-DD'); -- both zero -> Jan 1
+ to_date
+------------
+ 01-01-2024
+(1 row)
+
+SELECT to_timestamp('2024-00-15', 'YYYY-MM-DD'); -- same behavior for to_timestamp
+ to_timestamp
+------------------------------
+ Mon Jan 15 00:00:00 2024 PST
+(1 row)
+
+SELECT to_timestamp('2024-01-00', 'YYYY-MM-DD');
+ to_timestamp
+------------------------------
+ Mon Jan 01 00:00:00 2024 PST
+(1 row)
+
SELECT to_date('2016-13-10', 'YYYY-MM-DD');
ERROR: date/time field value out of range: "2016-13-10"
SELECT to_date('2016-02-30', 'YYYY-MM-DD');
diff --git a/src/test/regress/sql/horology.sql b/src/test/regress/sql/horology.sql
index 8978249a5dc..48bbf14a210 100644
--- a/src/test/regress/sql/horology.sql
+++ b/src/test/regress/sql/horology.sql
@@ -660,6 +660,15 @@ SELECT to_timestamp('1000000000,999', 'Y,YYY');
SELECT to_timestamp('0.-2147483648', 'SS.MS');
SELECT to_timestamp('613566758', 'W');
SELECT to_timestamp('2024 613566758 1', 'YYYY WW D');
+-- Zero month/day values are treated as missing data and default to 1
+-- (see ZERO_tm() in formatting.c which initializes tm_mon = tm_mday = 1,
+-- and do_to_timestamp() which only overwrites them for non-zero parsed values)
+SELECT to_date('2024-00-15', 'YYYY-MM-DD'); -- month 0 -> defaults to January
+SELECT to_date('2024-01-00', 'YYYY-MM-DD'); -- day 0 -> defaults to day 1
+SELECT to_date('2024-00-00', 'YYYY-MM-DD'); -- both zero -> Jan 1
+SELECT to_timestamp('2024-00-15', 'YYYY-MM-DD'); -- same behavior for to_timestamp
+SELECT to_timestamp('2024-01-00', 'YYYY-MM-DD');
+
SELECT to_date('2016-13-10', 'YYYY-MM-DD');
SELECT to_date('2016-02-30', 'YYYY-MM-DD');
SELECT to_date('2016-02-29', 'YYYY-MM-DD'); -- ok
--
2.34.1
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: to_date()/to_timestamp() silently accept month=0 and day=0
In-Reply-To: <CAJTYsWVEk5vdy8of2DCWzDNJgHUNL0p0Dt6b1zkgmvmNo3ht6w@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox