BUG #17794: dates with zero or negative years are not accepted - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      17794
Logged by:          Richard Neill
Email address:      postgresql@richardneill.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.


Rationale:

If I understand rightly, I believe that both these formats are valid
ISO_8601, and that Postgres should accept them. (Note that Year 0 is  1 BC,
and that Year -1 is 2 BC).
https://en.wikipedia.org/wiki/ISO_8601
https://en.wikipedia.org/wiki/Year_zero

For comparison, I tested the behaviour of various other languages: Postgres,
GNU date, JS, and PHP are all slightly inconsistent with each other, but I
hope the set of tests below is useful.


POSTGRES:

select '0001-01-02' :: date     -> 0001-01-02
select '0000-01-02' :: date     -> date/time field value out of range:
"0000-01-02"
select '-0001-01-02' :: date    -> invalid input syntax for type date:
"-0001-01-02"
select make_date(0001,1,2);     -> 0001-01-02
select make_date(0000,1,2);     -> ERROR:  date field value out of range:
0-01-02
select make_date(-0001,1,2);    -> 0001-01-02 BC 
select to_timestamp(-62135510325); -> 0001-01-02 00:00:00-00:01:15 
select to_timestamp(-62167132725); -> 0001-01-02 00:00:00-00:01:15 BC
select to_timestamp(-62198668725); ->  0002-01-02 00:00:00-00:01:15 BC

GNU DATE:

date +%Y-%m-%d -d '0001-01-02'   -> 0001-01-02
date +%Y-%m-%d -d '0000-01-02'   -> 0000-01-02
date +%Y-%m-%d -d '-0001-01-02'  -> date: invalid date ‘-0001-01-02’
date +%Y-%m-%d -d @-62135510325  -> 0001-01-02
date +%Y-%m-%d -d @-62167132725  -> 0000-01-02
date +%Y-%m-%d -d @-62198668725  -> -001-01-02
date +%Y-%m-%d -d '-001-01-02'   -> date: invalid date ‘-001-01-02’

PHP:

date("Y-m-d", -62135510325) ;    -> 0001-01-02               
date("Y-m-d", -62167132725) ;    -> 0000-01-02
date("Y-m-d", -62198668725) ;    -> -0001-01-02               

date("Y-m-d", strtotime("0001-01-02")) ; -> 0001-01-02
date("Y-m-d", strtotime("0000-01-02")) ; -> 0000-01-02
date("Y-m-d", strtotime("-0001-01-02")) ; -> -0001-01-02 


JAVASCRIPT  (N.B. factor 1000 as JS works in ms)

console.log(Date.parse('0001-01-02'));  -> -62135510400000
console.log(Date.parse('0000-01-02'));  -> -62167132800000
console.log(Date.parse('-0001-01-02'));  -> NaN

console.log(new Date(-62135510325000).toString()) -> "Tue Jan 02 0001
00:00:00 GMT-0001 (Greenwich Mean Time)"

console.log(new Date(-62167132725000).toString()) -> "Sun Jan 02 0000
00:00:00 GMT-0001 (Greenwich Mean Time)"

console.log(new Date(-62198668725).toString()) ->  > "Sat Jan 02 -0001
00:00:00 GMT-0001 (Greenwich Mean Time)"


Finally, there is no example of handling negative years here:
https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-DATETIME-INPUT


Thank you very much!