Обсуждение: Question on cast string to date

2022年5月10日(火) 13:28 正华吕 <kainwen@gmail.com>:
>
> Hi,
>
>     I test the following SQL in pg15dev (seems same behavior as the previous version).
>
>     select '2020701'::date;
>     date
>    ------------
>     0202-07-01
>    (1 row)
>
>    At the first glance, the result seems quite strange.
>
>    Go through the code, postgres use date_in to do the cast, and firstly use last 2 chars to
>    get the day, and the 2 chars to get the month, and all remaining chars as year.
>
>    The question here is: should we throw error for such input? Or what standard postgres is
>    using to cast such kind of string?

This is ISO-8601 format, see here for a list of possible input formats:

  https://www.postgresql.org/docs/current/datatype-datetime.html#id-1.5.7.13.18.5


Regards

Ian Barwick

--
EnterpriseDB: https://www.enterprisedb.com

=?UTF-8?B?5q2j5Y2O5ZCV?= <kainwen@gmail.com> writes:
>     I test the following SQL in pg15dev (seems same behavior as the
> previous version).
>     select '2020701'::date;
>     date
>    ------------
>     0202-07-01
>    (1 row)
>    At the first glance, the result seems quite strange.

[ shrug... ]  You left out a zero.  It's not apparent to me that
this answer is wrong.

>    The question here is: should we throw error for such input? Or what
> standard postgres is
>    using to cast such kind of string?

The bar to changing any behavior here is a *lot* higher than
you seem to imagine.

Having said that, it does appear that we changed this somewhere
between 9.3 and 9.4:

psql (9.3.25)
Type "help" for help.

regression=# select '2020701'::date;
ERROR:  invalid input syntax for type date: "2020701"
LINE 1: select '2020701'::date;
               ^

psql (9.4.26)
Type "help" for help.

regression=# select '2020701'::date;
    date    
------------
 0202-07-01
(1 row)

If you want to pursue this question, you could start by bisecting
to find just which commit changed it and why.

            regards, tom lane

正华吕 <kainwen@gmail.com> writes:
>     I test the following SQL in pg15dev (seems same behavior as the
> previous version).
>     select '2020701'::date;
>     date
>    ------------
>     0202-07-01
>    (1 row)
>    At the first glance, the result seems quite strange.

[ shrug... ]  You left out a zero.  It's not apparent to me that
this answer is wrong. 

If you want to pursue this question, you could start by bisecting
to find just which commit changed it and why.

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Monday, May 9, 2022, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> If you want to pursue this question, you could start by bisecting
>> to find just which commit changed it and why.

> Manual history inspection of datetime.c
> https://github.com/postgres/postgres/commit/7778ddc7a2d5b006edbfa69cdb44b8d8c24ec1ff

Ah, yeah, that looks plausible -- the previous code allowed 6 digits
YYMMDD or 8 digits YYYYMMDD, the new code allowed >= 6 digits with
2 or more YY followed by MMDD.  So the specific case of YYYMMDD
was rejected before and not after.  Doesn't seem to have been planned,
but it's not so obviously inconsistent that I'd care to break it
again nine years later.  By now, somebody might be depending on it.

            regards, tom lane