Question on cast string to date

út 10. 5. 2022 v 6:28 odesílatel 正华吕 <kainwen@gmail.com> napsal:

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 format

https://en.wikipedia.org/wiki/ISO_8601

https://postgresqlco.nf/doc/en/param/DateStyle/

Regards

Pavel

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

On Monday, May 9, 2022, Tom Lane <tgl@sss.pgh.pa.us> wrote:

=?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.

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

/messages/by-id/8977CB36860C5843884E0A18D8747B0372BC6401@szxeml558-mbs.china.huawei.com

The goal seemed to be able to accept 5-digit years…this behavior change
didn’t show in the tests (or discussion) though I didn’t look for the of
testing the pre-existing failure mode.

David J.

Thanks all.

David G. Johnston <david.g.johnston@gmail.com> 于2022年5月10日周二 13:41写道：

"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