> t...@sss.pgh.pa.us wrote:
>
> Adrian Klaver <adrian.kla...@aklaver.com> 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');
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".
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.
