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

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: postgresql(at)richardneill(dot)org
Subject: BUG #17794: dates with zero or negative years are not accepted
Date: 2023-02-15 04:21:54
Message-ID: 17794-1ddd1f50f9df08a1@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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.

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!

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Andres Freund 2023-02-15 05:06:12 Re: BUG #17791: Assert on procarray.c
Previous Message Robins Tharakan 2023-02-15 04:16:13 Re: BUG #17791: Assert on procarray.c