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