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

On 11/3/21 1:39 PM, Bryn Llewellyn wrote:
> /adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.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.klaver@aklaver.com