public inbox for [email protected]
help / color / mirror / Atom feedRe: to_date()/to_timestamp() silently accept month=0 and day=0
6+ messages / 3 participants
[nested] [flat]
* Re: to_date()/to_timestamp() silently accept month=0 and day=0
@ 2026-04-24 08:16 Ayush Tiwari <[email protected]>
2026-04-24 09:13 ` Re: to_date()/to_timestamp() silently accept month=0 and day=0 Ayush Tiwari <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Ayush Tiwari @ 2026-04-24 08:16 UTC (permalink / raw)
To: Daniel Gustafsson <[email protected]>; +Cc: Michael Paquier <[email protected]>; Tom Lane <[email protected]>; [email protected]
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.
Regards,
Ayush
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: to_date()/to_timestamp() silently accept month=0 and day=0
2026-04-24 08:16 Re: to_date()/to_timestamp() silently accept month=0 and day=0 Ayush Tiwari <[email protected]>
@ 2026-04-24 09:13 ` Ayush Tiwari <[email protected]>
2026-04-24 12:44 ` Re: to_date()/to_timestamp() silently accept month=0 and day=0 Daniel Gustafsson <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Ayush Tiwari @ 2026-04-24 09:13 UTC (permalink / raw)
To: Daniel Gustafsson <[email protected]>; +Cc: Michael Paquier <[email protected]>; Tom Lane <[email protected]>; [email protected]
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: to_date()/to_timestamp() silently accept month=0 and day=0
2026-04-24 08:16 Re: to_date()/to_timestamp() silently accept month=0 and day=0 Ayush Tiwari <[email protected]>
2026-04-24 09:13 ` Re: to_date()/to_timestamp() silently accept month=0 and day=0 Ayush Tiwari <[email protected]>
@ 2026-04-24 12:44 ` Daniel Gustafsson <[email protected]>
2026-04-24 12:50 ` Re: to_date()/to_timestamp() silently accept month=0 and day=0 Ayush Tiwari <[email protected]>
2026-04-27 07:23 ` Re: to_date()/to_timestamp() silently accept month=0 and day=0 Michael Paquier <[email protected]>
0 siblings, 2 replies; 6+ messages in thread
From: Daniel Gustafsson @ 2026-04-24 12:44 UTC (permalink / raw)
To: Ayush Tiwari <[email protected]>; +Cc: Michael Paquier <[email protected]>; Tom Lane <[email protected]>; [email protected]
> On 24 Apr 2026, at 11:13, Ayush Tiwari <[email protected]> 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
Attachments:
[application/octet-stream] v2-0001-Add-regression-tests-for-zero-month-day-handling-.patch (2.7K, 2-v2-0001-Add-regression-tests-for-zero-month-day-handling-.patch)
download | inline diff:
From dc0b7a09e0e02c587ba594d5af741c14186969e1 Mon Sep 17 00:00:00 2001
From: Daniel Gustafsson <[email protected]>
Date: Fri, 24 Apr 2026 14:37:05 +0200
Subject: [PATCH v2] Add regression tests for zero month/day handling in
to_date
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.
Author: Ayush Tiwari <[email protected]>
Discussion: https://postgr.es/m/CAJTYsWWNfF+hMpbsTDF8NMr+AuqoDfNxR=oHUZm7xqGP+dJ9rA@mail.gmail.com
---
src/test/regress/expected/horology.out | 25 +++++++++++++++++++++++++
src/test/regress/sql/horology.sql | 5 +++++
2 files changed, 30 insertions(+)
diff --git a/src/test/regress/expected/horology.out b/src/test/regress/expected/horology.out
index 32cf62b6741..6c8f4b1c19d 100644
--- a/src/test/regress/expected/horology.out
+++ b/src/test/regress/expected/horology.out
@@ -3818,6 +3818,31 @@ SELECT to_date('0000-02-01','YYYY-MM-DD'); -- allowed, though it shouldn't be
02-01-0001 BC
(1 row)
+-- Zero month/day values are treated as missing data and default to 1
+SELECT to_date('2024-00-15', 'YYYY-MM-DD'); -- ok, 0 -> January
+ to_date
+------------
+ 01-15-2024
+(1 row)
+
+SELECT to_date('2024-01-00', 'YYYY-MM-DD'); -- ok, 0 -> 1'st
+ to_date
+------------
+ 01-01-2024
+(1 row)
+
+SELECT to_date('100-00-00', 'YYY-MM-DD'); -- ok, 0/0 -> Jan 1st, year 100 -> 2100
+ to_date
+------------
+ 01-01-2100
+(1 row)
+
+SELECT to_date('100', 'YYY'); -- ok, equivalent to above
+ to_date
+------------
+ 01-01-2100
+(1 row)
+
SELECT to_date('100000000', 'CC');
ERROR: date/time field value out of range: "100000000"
SELECT to_date('-100000000', 'CC');
diff --git a/src/test/regress/sql/horology.sql b/src/test/regress/sql/horology.sql
index 8978249a5dc..ec4ef6814a5 100644
--- a/src/test/regress/sql/horology.sql
+++ b/src/test/regress/sql/horology.sql
@@ -670,6 +670,11 @@ SELECT to_date('2016 365', 'YYYY DDD'); -- ok
SELECT to_date('2016 366', 'YYYY DDD'); -- ok
SELECT to_date('2016 367', 'YYYY DDD');
SELECT to_date('0000-02-01','YYYY-MM-DD'); -- allowed, though it shouldn't be
+-- Zero month/day values are treated as missing data and default to 1
+SELECT to_date('2024-00-15', 'YYYY-MM-DD'); -- ok, 0 -> January
+SELECT to_date('2024-01-00', 'YYYY-MM-DD'); -- ok, 0 -> 1'st
+SELECT to_date('100-00-00', 'YYY-MM-DD'); -- ok, 0/0 -> Jan 1st, year 100 -> 2100
+SELECT to_date('100', 'YYY'); -- ok, equivalent to above
SELECT to_date('100000000', 'CC');
SELECT to_date('-100000000', 'CC');
SELECT to_date('-2147483648 01', 'CC YY');
--
2.39.3 (Apple Git-146)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: to_date()/to_timestamp() silently accept month=0 and day=0
2026-04-24 08:16 Re: to_date()/to_timestamp() silently accept month=0 and day=0 Ayush Tiwari <[email protected]>
2026-04-24 09:13 ` Re: to_date()/to_timestamp() silently accept month=0 and day=0 Ayush Tiwari <[email protected]>
2026-04-24 12:44 ` Re: to_date()/to_timestamp() silently accept month=0 and day=0 Daniel Gustafsson <[email protected]>
@ 2026-04-24 12:50 ` Ayush Tiwari <[email protected]>
1 sibling, 0 replies; 6+ messages in thread
From: Ayush Tiwari @ 2026-04-24 12:50 UTC (permalink / raw)
To: Daniel Gustafsson <[email protected]>; +Cc: Michael Paquier <[email protected]>; Tom Lane <[email protected]>; [email protected]
On Fri, 24 Apr 2026 at 18:14, Daniel Gustafsson <[email protected]> wrote:
> > On 24 Apr 2026, at 11:13, Ayush Tiwari <[email protected]>
> 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.
>
Looks good to me. Thank you!
Regards,
Ayush
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: to_date()/to_timestamp() silently accept month=0 and day=0
2026-04-24 08:16 Re: to_date()/to_timestamp() silently accept month=0 and day=0 Ayush Tiwari <[email protected]>
2026-04-24 09:13 ` Re: to_date()/to_timestamp() silently accept month=0 and day=0 Ayush Tiwari <[email protected]>
2026-04-24 12:44 ` Re: to_date()/to_timestamp() silently accept month=0 and day=0 Daniel Gustafsson <[email protected]>
@ 2026-04-27 07:23 ` Michael Paquier <[email protected]>
2026-04-27 10:54 ` Re: to_date()/to_timestamp() silently accept month=0 and day=0 Ayush Tiwari <[email protected]>
1 sibling, 1 reply; 6+ messages in thread
From: Michael Paquier @ 2026-04-27 07:23 UTC (permalink / raw)
To: Daniel Gustafsson <[email protected]>; +Cc: Ayush Tiwari <[email protected]>; Tom Lane <[email protected]>; [email protected]
On Fri, Apr 24, 2026 at 02:44:04PM +0200, Daniel Gustafsson wrote:
> 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.
That seems fine to me. If we decide to change this behavior later on
and error on these pattern, at least we'll know about them.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (833B, 2-signature.asc)
download
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: to_date()/to_timestamp() silently accept month=0 and day=0
2026-04-24 08:16 Re: to_date()/to_timestamp() silently accept month=0 and day=0 Ayush Tiwari <[email protected]>
2026-04-24 09:13 ` Re: to_date()/to_timestamp() silently accept month=0 and day=0 Ayush Tiwari <[email protected]>
2026-04-24 12:44 ` Re: to_date()/to_timestamp() silently accept month=0 and day=0 Daniel Gustafsson <[email protected]>
2026-04-27 07:23 ` Re: to_date()/to_timestamp() silently accept month=0 and day=0 Michael Paquier <[email protected]>
@ 2026-04-27 10:54 ` Ayush Tiwari <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Ayush Tiwari @ 2026-04-27 10:54 UTC (permalink / raw)
To: Michael Paquier <[email protected]>; +Cc: Daniel Gustafsson <[email protected]>; Tom Lane <[email protected]>; [email protected]
Hi,
On Mon, 27 Apr 2026 at 12:53, Michael Paquier <[email protected]> wrote:
> On Fri, Apr 24, 2026 at 02:44:04PM +0200, Daniel Gustafsson wrote:
> > 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.
>
> That seems fine to me. If we decide to change this behavior later on
> and error on these pattern, at least we'll know about them.
>
+1.
(Just one tiny nit for whenever this gets committed: in the v2 inline
comment,
"0 -> 1'st" might be slightly cleaner as "0 -> 1st" or "0 -> 1").
Regards,
Ayush
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2026-04-27 10:54 UTC | newest]
Thread overview: 6+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-04-24 08:16 Re: to_date()/to_timestamp() silently accept month=0 and day=0 Ayush Tiwari <[email protected]>
2026-04-24 09:13 ` Ayush Tiwari <[email protected]>
2026-04-24 12:44 ` Daniel Gustafsson <[email protected]>
2026-04-24 12:50 ` Ayush Tiwari <[email protected]>
2026-04-27 07:23 ` Michael Paquier <[email protected]>
2026-04-27 10:54 ` Ayush Tiwari <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox