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

On 11/3/21 1:39 PM, Bryn Llewellyn wrote:
> /adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com> wrote:/
>
>> /Bryn wrote:/
>>

>>> 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 version();
version

------------------------------------------------------------------------------------
PostgreSQL 14.0 on x86_64-pc-linux-gnu, compiled by gcc (SUSE Linux)
7.5.0, 64-bit

>
> select make_timestamp(0,1,1,0,0,0);
ERROR: date field value out of range: 0-01-01

> select make_timestamp(-0,1,1,0,0,0);
ERROR: date field value out of range: 0-01-01

>
> select '-0001-01-01'::date; -- and similar for typecasts to timestamp
> and timestamptz
ERROR: invalid input syntax for type date: "-0001-01-01"
LINE 1: select '-0001-01-01'::date;

>
> select '0000-01-01'::date;
ERROR: date/time field value out of range: "0000-01-01"
LINE 1: select '0000-01-01'::date;

>
> select '-0000-01-01'::date;
ERROR: invalid input syntax for type date: "-0000-01-01"
LINE 1: 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;
>

a1 | a2 | - | b1 | b2
---------------+------------+---+------------+---------------
2021-06-15 BC | 2021-06-15 | | 2021-06-15 | 2021-06-15 BC

Haven't had time to work through what the above is actually doing.

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com