On 11/3/21 15:56, Bryn Llewellyn wrote:
tgl@xxxxxxxxxxxxx wrote:
Adrian Klaver <adrian.klaver@xxxxxxxxxxx> writes:
Haven't had time to work through what the above is actually doing.
I think the first two are explained by 489c9c340:
Also, arrange for the combination of a negative year and an
explicit "BC" marker to cancel out and produce AD. This is how
the negative-century case works, so it seems sane to do likewise.
The last two look like a parsing issue: with no field separator (nope, the space doesn't count), the code is taking the dash as a field separator.
Thanks, Adrian, for trying those tests. So if PG is aiming for one consistent story for years that are less than one, in all three APIs (to_date() and its cousins, make_timestamp[tz](), and text-to-date-time typecasts), then work still remains.
The parsing problem seems to be a separable annoyance. I assume that Tom’s "nope, the space doesn't count” is a narrow comment on this corner of the implementation. It definitely counts here:
select to_date('12345 01 01', 'YYYY MM DD');
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:
select to_date('12340101', 'YYYYMMDD');
this isn't:
select to_date('123450101', 'YYYYMMDD');
It does with a tweak:
select to_date('12345 0101', 'FMYYYYMMDD');
to_date
-------------
12345-01-01
and nor is anything else that you might dream up that does not have a separator as mentioned above.
Tom asked about Oracle Database. I have a still-working Version 18.0 in a VM on my laptop. The query can't be so terse there because there's no implicit typecast from date to text. And there's the famous annoyance of "dual".
If you go here:
https://www.postgresql.org/docs/current/functions-formatting.html
and search on Oracle you will see that there are other exceptions. Like
most things there is not complete agreement on how closely to follow
someone else's code.
I tried this first:
select to_char(to_date('1234/01/01', 'YYYY/MM/DD'), 'YYYY/MM/DD') from dual;
It gets the same output back as the input you gave. So far so good. Then I changed the input to '-1234/01/01'. It caused this error:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
So it seems that Oracle Database has its own problems. But at least the wording "must… not be 0" is clear—and not what PG wants to support.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
