Re: BUG #16419: wrong parsing BC year in to_date() function
От | Laurenz Albe |
---|---|
Тема | Re: BUG #16419: wrong parsing BC year in to_date() function |
Дата | |
Msg-id | 0123b852ae3fe53eca11b9451d035411b06dafd5.camel@cybertec.at обсуждение исходный текст |
Ответ на | Re: BUG #16419: wrong parsing BC year in to_date() function ("David G. Johnston" <david.g.johnston@gmail.com>) |
Ответы |
Re: BUG #16419: wrong parsing BC year in to_date() function
("David G. Johnston" <david.g.johnston@gmail.com>)
|
Список | pgsql-hackers |
On Tue, 2020-05-12 at 18:09 -0700, David G. Johnston wrote: > Redirecting to -hackers for visibility. I feel there needs to be something done here, even if just documentation (a bulletin the usage notes section - and a code comment update for the macro) > pointing this out and not changing any behavior. > > David J. > > On Wed, May 6, 2020 at 8:12 PM David G. Johnston <david.g.johnston@gmail.com> wrote: > > On Wed, May 6, 2020 at 6:31 PM دار الآثار للنشر والتوزيع-صنعاء Dar Alathar-Yemen <dar_alathar@hotmail.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 otherdatabase 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 yearsbe 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 valueby 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" inmake_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. > > > > > > > [and the next email] > > > Specifically: > > > > https://github.com/postgres/postgres/blob/fb544735f11480a697fcab791c058adc166be1fa/src/backend/utils/adt/formatting.c#L236 > > > > /* > > * There is no 0 AD. Years go from 1 BC to 1 AD, so we make it > > * positive and map year == -1 to year zero, and shift all negative > > * years up one. For interval years, we just return the year. > > */ > > #define ADJUST_YEAR(year, is_interval) ((is_interval) ? (year) : ((year) <= 0 ? -((year) - 1) : (year))) > > > > The code comment took me a bit to process - seems like the following would be better (if its right - I don't know whyinterval is a pure no-op while non-interval normalizes to a positive integer). > > > > Years go from 1 BC to 1 AD, so we adjust the year zero, and all negative years, by shifting them away one year, We thenreturn the positive value of the result because the caller tracks the BC/AD > > aspect of the year separately and only deals with positive year values coming out of this macro. Intervals denote thedistance away from 0 a year is so we can simply take the supplied value and > > return it. Interval processing code expects a negative result for intervals going into BC. > > > > David J. Since "to_date" is an Oracle compatibility function, here is what Oracle 18.4 has to say to that: SQL> SELECT to_date('0000', 'YYYY') FROM dual; SELECT to_date('0000', 'YYYY') FROM dual * ERROR at line 1: ORA-01841: (full) year must be between -4713 and +9999, and not be 0 SQL> SELECT to_date('-0001', 'YYYY') FROM dual; SELECT to_date('-0001', 'YYYY') FROM dual * ERROR at line 1: ORA-01841: (full) year must be between -4713 and +9999, and not be 0 SQL> SELECT to_date('-0001', 'SYYYY') FROM dual; TO_DATE('-0001','SYYYY ---------------------- 0001-05-01 00:00:00 BC Yours, Laurenz Albe
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Amit LangoteДата:
Сообщение: Re: making update/delete of inheritance trees scale better