Обсуждение: to_date()/to_timestamp() silently accept month=0 and day=0

On Wed, Apr 22, 2026 at 07:48:00PM +0530, Ayush Tiwari wrote:
> 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.

While I agree with your feeling that it would be less confusing if
these patterns are rejected, throwing an error could also mean an
impact on existing applications that relied on the existing historical
behavior of replacing these zeroes defined in input, where they'd
expect a 01.  So that would be a silent behavior change introduced in
a minor release.

Perhaps we could consider strengthening such inputs on HEAD once v20
opens for business?  It would be really a scary thing to backpatch,
still a major release is a different thing.

Any thoughts or opinions from others?
--
Michael

> On 23 Apr 2026, at 09:57, Michael Paquier <michael@paquier.xyz> wrote:

> Perhaps we could consider strengthening such inputs on HEAD once v20
> opens for business?  It would be really a scary thing to backpatch,
> still a major release is a different thing.

This could definitely not be backpatched IMO, a quick check in v14 shows the
same behaviour.  The gregorian calendar goes from BC1 to AD1 and does not
define a year 0, to_date('0000','YYYY') correctly returns year 0001, handling
months/days in the same way at least makes it consistent (though I didn't scour
the archives to see if it was intentionally done like that).

--
Daniel Gustafsson

Daniel Gustafsson <daniel@yesql.se> writes:
>> On 23 Apr 2026, at 09:57, Michael Paquier <michael@paquier.xyz> wrote:
>> Perhaps we could consider strengthening such inputs on HEAD once v20
>> opens for business?  It would be really a scary thing to backpatch,
>> still a major release is a different thing.

> This could definitely not be backpatched IMO, a quick check in v14 shows the
> same behaviour.  The gregorian calendar goes from BC1 to AD1 and does not
> define a year 0, to_date('0000','YYYY') correctly returns year 0001, handling
> months/days in the same way at least makes it consistent (though I didn't scour
> the archives to see if it was intentionally done like that).

Looking at the code, I think it intentionally interprets zero as
"missing data".  See for example the stanza at formatting.c:4650ff
where tm_mon and tm_mday can be backfilled from a DDD field.

I'm disinclined to change the behavior around this; you're far
more likely to get complaints than kudos.

            regards, tom lane

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.
--
Michael

> On 24 Apr 2026, at 00:14, Michael Paquier <michael@paquier.xyz> 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?

--
Daniel Gustafsson

> 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

On 23.04.26 17:40, Tom Lane wrote:
> Daniel Gustafsson <daniel@yesql.se> writes:
>>> On 23 Apr 2026, at 09:57, Michael Paquier <michael@paquier.xyz> wrote:
>>> Perhaps we could consider strengthening such inputs on HEAD once v20
>>> opens for business?  It would be really a scary thing to backpatch,
>>> still a major release is a different thing.
> 
>> This could definitely not be backpatched IMO, a quick check in v14 shows the
>> same behaviour.  The gregorian calendar goes from BC1 to AD1 and does not
>> define a year 0, to_date('0000','YYYY') correctly returns year 0001, handling
>> months/days in the same way at least makes it consistent (though I didn't scour
>> the archives to see if it was intentionally done like that).
> 
> Looking at the code, I think it intentionally interprets zero as
> "missing data".  See for example the stanza at formatting.c:4650ff
> where tm_mon and tm_mday can be backfilled from a DDD field.
> 
> I'm disinclined to change the behavior around this; you're far
> more likely to get complaints than kudos.

Complaints from whom?  Oracle rejects these, and PostgreSQL generally 
also rejects these dates/times in other contexts.  I think this should 
be rejected.

On Fri, Apr 24, 2026 at 05:41:50PM +0200, Peter Eisentraut wrote:
> Complaints from whom?  Oracle rejects these, and PostgreSQL generally also
> rejects these dates/times in other contexts.  I think this should be
> rejected.

From folks who hypothetically rely on the existing zeroed inputs.  :)
Perhaps it's a far-fetched argument, and just to be clear I would not
really object to a change of behavior on HEAD for v20, just to see
where it goes in 2027.
--
Michael

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