Re: to_date() and to_timestamp() with negative years
| От | Bryn Llewellyn |
|---|---|
| Тема | Re: to_date() and to_timestamp() with negative years |
| Дата | |
| Msg-id | 1EA95C37-F94C-4914-A7B1-E5C7FC65CA95@yugabyte.com обсуждение исходный текст |
| Ответ на | Re: to_date() and to_timestamp() with negative years (Adrian Klaver <adrian.klaver@aklaver.com>) |
| Ответы |
Re: to_date() and to_timestamp() with negative years
|
| Список | pgsql-general |
adrian.klaver@aklaver.com wrote:On 11/3/21 17:00, Adrian Klaver wrote:On 11/3/21 15:56, Bryn Llewellyn wrote:tgl@sss.pgh.pa.us wrote:And you have to have some kind of separator between the years substring and the adjacent one(s) even to succeed with years that have more than four digits. Another usage note stresses that while this is OK:It does with a tweak:
select to_date('12340101', 'YYYYMMDD');
this isn't:
select to_date('123450101', 'YYYYMMDD');
select to_date('12345 0101', 'FMYYYYMMDD');
to_date
-------------
12345-01-01
Well that was just plain wrong. I was not paying attention.
I'm missing your point, Adrian. But I must confess that I'm guilty of not expressing myself clearly with my reference to years with more than four digits. I meant that reference only to make the point that, in that use-case, a space CAN act as a separator.
By the way, I'd been studying the "Usage notes for date/time formatting" section in the Version 11 PG docs because that's the PG version that YugabyteDB uses. It has eleven bullets. The corresponding section in the "Current" PG docs has just eight bullets and the overall wording of the section is quite different. However, the bullet, in the Version 11 docs, that started me on the testing that spurred my first email in this thread is identically worded in both versions. It says this:
«
In to_timestamp and to_date, negative years are treated as signifying BC. If you write both a negative year and an explicit BC field, you get AD again. An input of year zero is treated as 1 BC.
»
Back to the point about separators, the "Current" doc has this bullet:
«
A separator (a space or non-letter/non-digit character) in the template string of to_timestamp and to_date matches any single separator in the input string or is skipped, unless the FX option is used...
»
(There's no such explanation in the Version 11 doc—but never mind that.) I read this to mean that a space IS a viable separator. And yet Tom's "nope, the space doesn't count [as a separator]" claims the opposite. The bullet's wording, by the way, says that the minus sign is a separator. But yet it can also be taken to confer the meaning "BC" to a year. No wonder I'm confused.
Elsewhere the "Current" doc says that runs of two or more spaces have the same effect as a single space (in the absence of FX or FM complexity).
No wonder that examples like I showed sometimes produce the wrong results, even after more than one round of tinkering with the C implementation. Try this test (the to-be-converted text has runs of five spaces, and the template has runs of ten spaces):
select to_date(' 1950 02 14', 'YYYY MM DD');
select to_date(' -1950 02 14', 'YYYY MM DD');
select to_date(' 14 02 1950', 'DD MM YYYY');
select to_date(' 14 02 -1950', 'DD MM YYYY');
select to_date(' -1950 02 14', 'YYYY MM DD');
select to_date(' 14 02 1950', 'DD MM YYYY');
select to_date(' 14 02 -1950', 'DD MM YYYY');
"-1950" is taken as "BC" in the second to_date() but it is not so taken in the fourth to_date().
В списке pgsql-general по дате отправления: