From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Bryn Llewellyn <bryn(at)yugabyte(dot)com>, Tom Lane PostgreSQL <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-general list <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: to_date() and to_timestamp() with negative years |
Date: | 2021-11-03 19:37:38 |
Message-ID: | 72204b8f-41b2-0004-b09b-842f8c57477f@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 11/3/21 11:18, Bryn Llewellyn 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.
>
>
>
>
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Shaozhong SHI | 2021-11-03 19:44:36 | Regex for (A) and (B) to find in Bus Stop (A) or (B) |
Previous Message | Tom Lane | 2021-11-03 19:24:10 | Re: Selectivity and row count estimates for JSONB columns |