pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17794: dates with zero or negative years are not accepted
5+ messages / 4 participants
[nested] [flat]
* BUG #17794: dates with zero or negative years are not accepted
@ 2023-02-15 04:21 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 2 replies; 5+ messages in thread
From: PG Bug reporting form @ 2023-02-15 04:21 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: postgresql@richardneill.org
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!
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #17794: dates with zero or negative years are not accepted
@ 2023-02-15 22:06 David Rowley <dgrowleyml@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 1 reply; 5+ messages in thread
From: David Rowley @ 2023-02-15 22:06 UTC (permalink / raw)
To: postgresql@richardneill.org; pgsql-bugs@lists.postgresql.org
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?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #17794: dates with zero or negative years are not accepted
@ 2023-02-15 22:36 Tom Lane <tgl@sss.pgh.pa.us>
parent: David Rowley <dgrowleyml@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2023-02-15 22:36 UTC (permalink / raw)
To: David Rowley <dgrowleyml@gmail.com>; +Cc: postgresql@richardneill.org; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #17794: dates with zero or negative years are not accepted
@ 2023-02-15 23:03 David G. Johnston <david.g.johnston@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 1 reply; 5+ messages in thread
From: David G. Johnston @ 2023-02-15 23:03 UTC (permalink / raw)
To: postgresql@richardneill.org; pgsql-bugs@lists.postgresql.org
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.
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #17794: dates with zero or negative years are not accepted
@ 2023-02-16 00:08 Tom Lane <tgl@sss.pgh.pa.us>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2023-02-16 00:08 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: postgresql@richardneill.org; pgsql-bugs@lists.postgresql.org
"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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2023-02-16 00:08 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-02-15 04:21 BUG #17794: dates with zero or negative years are not accepted PG Bug reporting form <noreply@postgresql.org>
2023-02-15 22:06 ` David Rowley <dgrowleyml@gmail.com>
2023-02-15 22:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-02-15 23:03 ` David G. Johnston <david.g.johnston@gmail.com>
2023-02-16 00:08 ` Tom Lane <tgl@sss.pgh.pa.us>
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