public inbox for [email protected]
help / color / mirror / Atom feedFrom: Daniel Gustafsson <[email protected]>
To: Ayush Tiwari <[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:44:04 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAJTYsWVEk5vdy8of2DCWzDNJgHUNL0p0Dt6b1zkgmvmNo3ht6w@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>
<CAJTYsWVEk5vdy8of2DCWzDNJgHUNL0p0Dt6b1zkgmvmNo3ht6w@mail.gmail.com>
> 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)
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: <[email protected]>
* 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