Feedback-ID: ie0a040ee:Fastmail
Message-ID: <d77aae98-f563-4561-b30e-699924d4d9c6@eisentraut.org>
Date: Fri, 24 Apr 2026 17:41:50 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: to_date()/to_timestamp() silently accept month=0 and day=0
To: Tom Lane <tgl@sss.pgh.pa.us>, Daniel Gustafsson <daniel@yesql.se>
Cc: Michael Paquier <michael@paquier.xyz>,
 Ayush Tiwari <ayushtiwari.slg01@gmail.com>, pgsql-bugs@lists.postgresql.org
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>
Content-Language: en-US
From: Peter Eisentraut <peter@eisentraut.org>
In-Reply-To: <1444609.1776958814@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/d77aae98-f563-4561-b30e-699924d4d9c6%40eisentraut.org>
Precedence: bulk

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.