From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: postgresql@richardneill.org
Subject: BUG #17794: dates with zero or negative years are not accepted
Date: Wed, 15 Feb 2023 04:21:54 +0000
Message-ID: <17794-1ddd1f50f9df08a1@postgresql.org> (raw)
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_8601https://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!
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, postgresql@richardneill.org
Subject: Re: BUG #17794: dates with zero or negative years are not accepted
In-Reply-To: <17794-1ddd1f50f9df08a1@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox