Re: BUG #17794: dates with zero or negative years are not accepted

On Wed, 15 Feb 2023 at 20:54, PG Bug reporting form
<noreply(at)postgresql(dot)org> wrote:
> SELECT '0000-01-02' :: date
> => date/time field value out of range: "0000-01-02"
> I think it should be accepted as 2nd Jan, year 1 BC
>
> and similarly,
> SELECT '-0001-01-02' :: date
> => invalid input syntax for type date: "-0001-01-02"
> I think this should be accepted, to mean 2nd Jan, year 2 BC.

I don't think you could class these as bugs as we seem to be
explicitly disallowing it. However, I think I understand your
rationale for wanting this. My question to you now is; if someone
writes '-0001-01-01 BC' should that mean 1st of January 0002? And if
not, why are negative AD years more special than negative BC years?

From an implementation point of view, it looks trivial to just allow
0000 to mean 1 BC, however, the situation is more complex for negative
numbers as ParseDateTime() sees the negative sign and categorises that
portion as a timezone. The parsing would have to be adjusted to make
this be seen as a year, and that'll cause us to suddenly start
interpreting date strings differently from what we do now, which risks
breaking applications. I'm not sure that's worth the risk.

David