Re: to_date() and to_timestamp() with negative years

adrian(dot)klaver(at)aklaver(dot)com wrote:

> Bryn wrote:
>
>>> tgl(at)sss(dot)pgh(dot)pa(dot)us wrote:
>>>
>>> Bryn Llewellyn <bryn(at)yugabyte(dot)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;