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 BCand 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
On Wed, Feb 15, 2023 at 12:54 AM PG Bug reporting form <
noreply@postgresql.org> wrote:
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 expectedBut,
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 BCand 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.
"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