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

On Wed, Feb 15, 2023 at 12:54 AM PG Bug reporting form <
noreply(at)postgresql(dot)org> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 17794
> Logged by: Richard Neill
> Email address: postgresql(at)richardneill(dot)org
> PostgreSQL version: 14.5
> Operating system: Linux
> Description:
>
> SELECT '0001-01-02' :: date
> => gives 0001-01-02 as expected
>
> But,
> 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.
>
>
We just stopped doing this "shift-by-one" in probably the one arguable case
where doing so made sense.

https://github.com/postgres/postgres/commit/489c9c3407cbfd473c2f8d7863ffaaf6d2e8fcf8
We are not going to newly introduce it here.

As the others note, standardizing how to handle a requested year zero isn't
a reasonably achievable goal right now.

>
> Finally, there is no example of handling negative years here:
>
> https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-DATETIME-INPUT
>
>
Yes, this niche area could benefit from some attention.

David J.