From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | دار الآثار للنشر والتوزيع-صنعاء Dar Alathar-Yemen <dar_alathar(at)hotmail(dot)com> |
Cc: | "pgsql-bugs(at)lists(dot)postgresql(dot)org" <pgsql-bugs(at)lists(dot)postgresql(dot)org> |
Subject: | Re: BUG #16419: wrong parsing BC year in to_date() function |
Date: | 2020-05-07 03:12:20 |
Message-ID: | CAKFQuwaXESne_VYt8bE8L5gGGqEM0TCGL+JNL1a8NJ2_uc8VfQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs pgsql-hackers |
On Wed, May 6, 2020 at 6:31 PM دار الآثار للنشر والتوزيع-صنعاء Dar
Alathar-Yemen <dar_alathar(at)hotmail(dot)com> wrote:
> Any one suppose that these functions return the same:
> make_date(-1,1,1)
> to_date('-1-01-01','yyyy-mm-dd')
>
> But make_date will give 0001-01-01 BC
>
> And to_date will give 0002-01-01 BC
>
>
>
Interesting...and a fair point.
What seems to be happening here is that to_date is trying to be helpful by
doing:
select to_date('0000','YYYY'); // 0001-01-01 BC
It does this seemingly by subtracting one from the year, making it
positive, then (I infer) appending "BC" to the result. Thus for the year
"-1" it yields "0002-01-01 BC"
make_date just chooses to reject the year 0 and treat the negative as an
alternative to specifying BC
There seems to be zero tests for to_date involving negative years, and the
documentation doesn't talk of them.
I'll let the -hackers speak up as to how they want to go about handling
to_date (research how it behaves in the other database it tries to emulate
and either document or possibly change the behavior in v14) but do suggest
that a simple explicit description of how to_date works in the presence of
negative years be back-patched. A bullet in the usage notes section
probably suffices:
"If a YYYY format string captures a negative year, or 0000, it will treat
it as a BC year after decreasing the value by one. So 0000 maps to 1 BC
and -1 maps to 2 BC and so on."
So, no, make_date and to_date do not agree on this point; and they do not
have to. There is no way to specify "BC" in make_date function so using
negative there makes sense. You can specify BC in the input string for
to_date and indeed that is the only supported (documented) way to do so.
David J.
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2020-05-07 05:05:10 | Re: BUG #16419: wrong parsing BC year in to_date() function |
Previous Message | دار الآثار للنشر والتوزيع-صنعاء Dar Alathar-Yemen | 2020-05-07 00:59:28 | رد: BUG #16419: wrong parsing BC year in to_date() function |
From | Date | Subject | |
---|---|---|---|
Next Message | Fujii Masao | 2020-05-07 04:15:52 | Re: +(pg_lsn, int8) and -(pg_lsn, int8) operators |
Previous Message | Peter Geoghegan | 2020-05-07 03:03:04 | Should smgrdounlink() be removed? |