Обсуждение: 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
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 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.
We just stopped doing this "shift-by-one" in probably the one arguable case where doing so made sense.
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