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!
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 |