adrian.kla...@aklaver.com wrote:
> Bryn wrote:
>
>>> t...@sss.pgh.pa.us wrote:
>>>
>>> Bryn Llewellyn <b...@yugabyte.com> writes:
>>>> Is there any chance that you might be bold and simply make negative "year"
>>>> values illegal in "to_date()" and "to_timestamp()" — just as they already
>>>> are in "make_timestamp()", "make_timestamptz()", and the "from text"
>>>> typecasts to date-time moment values?
>>>
>>> Uh, what?
>>>
>>> regression=# select make_timestamp(-44,3,15,0,0,0);
>>> make_timestamp
>>> ------------------------
>>> 0044-03-15 00:00:00 BC
>>> (1 row)
>>>
>>> The other stuff you are talking about looks like confusion around which
>>> characters are minus signs in the data and which ones are field
>>> separators. Given the very squishy definitions of to_date/to_timestamp,
>>> I'm not surprised if that works only with carefully chosen field
>>> layouts --- but it does work for me with all of these cases:
>>>
>>> regression=# select to_date('-0044-03-15', 'YYYY-MM-DD');
>>> to_date
>>> ---------------
>>> 0044-03-15 BC
>>> (1 row)
>>>
>>> regression=# select to_date('03-15--0044', 'MM-DD-YYYY');
>>> to_date
>>> ---------------
>>> 0044-03-15 BC
>>> (1 row)
>>>
>>> regression=# select to_date('03/15/-0044', 'MM/DD/YYYY');
>>> to_date
>>> ---------------
>>> 0044-03-15 BC
>>> (1 row)
>>>
>>> I'd be the first to agree that that code is a mess and could stand to
>>> be rewritten --- but I seriously doubt that we'd take a patch that
>>> intentionally breaks cases that work fine today. There's also the
>>> angle that these are supposed to be Oracle-compatible, so I wonder
>>> what Oracle does with such input.
>> I just ran your test:
>> select make_timestamp(-44,3,15,0,0,0);
>> in each of the three environments that I mentioned—but especially,
>> therefore, in PG 13.4 (on macOS).
>> It cased the error that I mentioned:
>> ERROR: 22008: date field value out of range: -44-03-15
>> It's the same with "make_timestamp()".
>
> https://www.postgresql.org/docs/14/release-14.html#id-1.11.6.5.3
>
> "
> Allow make_timestamp()/make_timestamptz() to accept negative years (Peter
> Eisentraut)
>
> Negative values are interpreted as BC years.
> "
>
>> In what PG version did you run your test?
>> I'm not surprised that grandfathered-in "solution" that I described will
>> never go away—despite its questionable conceptual basis.
Thanks, Adrian. What results do these queries give in Version 14? (Forgive me,
I'll make time to install v14 very soon.)
select make_timestamp(0,1,1,0,0,0);
select make_timestamp(-0,1,1,0,0,0);
select '-0001-01-01'::date; -- and similar for typecasts to timestamp and
timestamptz
select '0000-01-01'::date;
select '-0000-01-01'::date;
select
to_date( '15/06/-2021', 'DD/MM/YYYY' ) as a1,
to_date( '15/06/-2021 BC', 'DD/MM/YYYY/AD' ) as a2,
'' as "-",
to_date( '15 06 -2021', 'DD MM YYYY' ) as b1,
to_date( '15 06 -2021 BC', 'DD MM YYYY AD' ) as b2;
