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

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!

On Wed, 15 Feb 2023 at 20:54, PG Bug reporting form
<noreply@postgresql.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

David Rowley <dgrowleyml@gmail.com> writes:
> 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.

Yeah, the real problem is that getting '-' to be seen as part of the
year field will cause havoc in the parsing rules.  I'd say if you
want this sort of input, use make_date() or make_timestamptz() rather
than going through string form.

            regards, tom lane

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> 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

Hmm, yeah, we should have suggested to_date as an alternative.  Its use
of a format string makes the parsing problem more tractable.

            regards, tom lane