Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tJywG-006AZA-8m for pgsql-bugs@arkaria.postgresql.org; Sat, 07 Dec 2024 17:49:56 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tJywC-00GFW4-0W for pgsql-bugs@arkaria.postgresql.org; Sat, 07 Dec 2024 17:49:53 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tJywB-00GFVv-Nr for pgsql-bugs@lists.postgresql.org; Sat, 07 Dec 2024 17:49:52 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tJyw9-001XrB-ET for pgsql-bugs@lists.postgresql.org; Sat, 07 Dec 2024 17:49:51 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 4B7Hnjce3281203; Sat, 7 Dec 2024 12:49:46 -0500 From: Tom Lane To: Nick Davies cc: "pgsql-bugs@lists.postgresql.org" Subject: Re: FF3 vs MS Date/Time Template - ERROR: date/time field value out of range In-reply-to: References: Comments: In-reply-to Nick Davies message dated "Fri, 06 Dec 2024 12:44:54 +0000" MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0" Content-ID: <3281106.1733593725.0@sss.pgh.pa.us> Date: Sat, 07 Dec 2024 12:49:45 -0500 Message-ID: <3281202.1733593785@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------- =_aaaaaaaaaa0 Content-Type: text/plain; charset="us-ascii" Content-ID: <3281106.1733593725.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Nick Davies writes: > In the docs it appears the 'MS' and 'FF3' date time template patterns ar= e identical but the behaviour is different. We also support Oracle which d= oesn't have 'MS', so we tried to use the pattern 'YYYYMMDDHH24MISSFF3'. Th= is doesn't work, but switching to 'MS' does. Thanks for the report! The problem seems to be that the FFn codes are mislabeled with is_digit =3D false. That causes the preceding SS code to think that it should eat all the remaining digits, so we end with seconds =3D 33123 which of course results in an error. I went through the other format codes to look for similar errors and didn't see any. regards, tom lane ------- =_aaaaaaaaaa0 Content-Type: text/x-diff; name="fix-FF-is_digit-labeling.patch"; charset="us-ascii" Content-ID: <3281106.1733593725.2@sss.pgh.pa.us> Content-Description: fix-FF-is_digit-labeling.patch Content-Transfer-Encoding: quoted-printable diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/fo= rmatting.c index 2bcc185708..9ec8769eb9 100644 --- a/src/backend/utils/adt/formatting.c +++ b/src/backend/utils/adt/formatting.c @@ -622,7 +622,7 @@ typedef enum DCH_Day, DCH_Dy, DCH_D, - DCH_FF1, + DCH_FF1, /* FFn codes must be consecutive */ DCH_FF2, DCH_FF3, DCH_FF4, @@ -788,12 +788,12 @@ static const KeyWord DCH_keywords[] =3D { {"Day", 3, DCH_Day, false, FROM_CHAR_DATE_NONE}, {"Dy", 2, DCH_Dy, false, FROM_CHAR_DATE_NONE}, {"D", 1, DCH_D, true, FROM_CHAR_DATE_GREGORIAN}, - {"FF1", 3, DCH_FF1, false, FROM_CHAR_DATE_NONE}, /* F */ - {"FF2", 3, DCH_FF2, false, FROM_CHAR_DATE_NONE}, - {"FF3", 3, DCH_FF3, false, FROM_CHAR_DATE_NONE}, - {"FF4", 3, DCH_FF4, false, FROM_CHAR_DATE_NONE}, - {"FF5", 3, DCH_FF5, false, FROM_CHAR_DATE_NONE}, - {"FF6", 3, DCH_FF6, false, FROM_CHAR_DATE_NONE}, + {"FF1", 3, DCH_FF1, true, FROM_CHAR_DATE_NONE}, /* F */ + {"FF2", 3, DCH_FF2, true, FROM_CHAR_DATE_NONE}, + {"FF3", 3, DCH_FF3, true, FROM_CHAR_DATE_NONE}, + {"FF4", 3, DCH_FF4, true, FROM_CHAR_DATE_NONE}, + {"FF5", 3, DCH_FF5, true, FROM_CHAR_DATE_NONE}, + {"FF6", 3, DCH_FF6, true, FROM_CHAR_DATE_NONE}, {"FX", 2, DCH_FX, false, FROM_CHAR_DATE_NONE}, {"HH24", 4, DCH_HH24, true, FROM_CHAR_DATE_NONE}, /* H */ {"HH12", 4, DCH_HH12, true, FROM_CHAR_DATE_NONE}, @@ -844,12 +844,12 @@ static const KeyWord DCH_keywords[] =3D { {"dd", 2, DCH_DD, true, FROM_CHAR_DATE_GREGORIAN}, {"dy", 2, DCH_dy, false, FROM_CHAR_DATE_NONE}, {"d", 1, DCH_D, true, FROM_CHAR_DATE_GREGORIAN}, - {"ff1", 3, DCH_FF1, false, FROM_CHAR_DATE_NONE}, /* f */ - {"ff2", 3, DCH_FF2, false, FROM_CHAR_DATE_NONE}, - {"ff3", 3, DCH_FF3, false, FROM_CHAR_DATE_NONE}, - {"ff4", 3, DCH_FF4, false, FROM_CHAR_DATE_NONE}, - {"ff5", 3, DCH_FF5, false, FROM_CHAR_DATE_NONE}, - {"ff6", 3, DCH_FF6, false, FROM_CHAR_DATE_NONE}, + {"ff1", 3, DCH_FF1, true, FROM_CHAR_DATE_NONE}, /* f */ + {"ff2", 3, DCH_FF2, true, FROM_CHAR_DATE_NONE}, + {"ff3", 3, DCH_FF3, true, FROM_CHAR_DATE_NONE}, + {"ff4", 3, DCH_FF4, true, FROM_CHAR_DATE_NONE}, + {"ff5", 3, DCH_FF5, true, FROM_CHAR_DATE_NONE}, + {"ff6", 3, DCH_FF6, true, FROM_CHAR_DATE_NONE}, {"fx", 2, DCH_FX, false, FROM_CHAR_DATE_NONE}, {"hh24", 4, DCH_HH24, true, FROM_CHAR_DATE_NONE}, /* h */ {"hh12", 4, DCH_HH12, true, FROM_CHAR_DATE_NONE}, diff --git a/src/test/regress/expected/horology.out b/src/test/regress/exp= ected/horology.out index 6d7dd5c988..8a68379578 100644 --- a/src/test/regress/expected/horology.out +++ b/src/test/regress/expected/horology.out @@ -3453,6 +3453,17 @@ SELECT i, to_timestamp('2018-11-02 12:34:56.123456'= , 'YYYY-MM-DD HH24:MI:SS.FF' = SELECT i, to_timestamp('2018-11-02 12:34:56.123456789', 'YYYY-MM-DD HH24:= MI:SS.FF' || i) FROM generate_series(1, 6) i; ERROR: date/time field value out of range: "2018-11-02 12:34:56.12345678= 9" +SELECT i, to_timestamp('20181102123456123456', 'YYYYMMDDHH24MISSFF' || i)= FROM generate_series(1, 6) i; + i | to_timestamp = +---+------------------------------------- + 1 | Fri Nov 02 12:34:56.1 2018 PDT + 2 | Fri Nov 02 12:34:56.12 2018 PDT + 3 | Fri Nov 02 12:34:56.123 2018 PDT + 4 | Fri Nov 02 12:34:56.1235 2018 PDT + 5 | Fri Nov 02 12:34:56.12346 2018 PDT + 6 | Fri Nov 02 12:34:56.123456 2018 PDT +(6 rows) + SELECT to_date('1 4 1902', 'Q MM YYYY'); -- Q is ignored to_date = ------------ diff --git a/src/test/regress/sql/horology.sql b/src/test/regress/sql/horo= logy.sql index 0fe3c783e6..8648163722 100644 --- a/src/test/regress/sql/horology.sql +++ b/src/test/regress/sql/horology.sql @@ -558,6 +558,7 @@ SELECT i, to_timestamp('2018-11-02 12:34:56.1234', 'YY= YY-MM-DD HH24:MI:SS.FF' || SELECT i, to_timestamp('2018-11-02 12:34:56.12345', 'YYYY-MM-DD HH24:MI:S= S.FF' || i) FROM generate_series(1, 6) i; SELECT i, to_timestamp('2018-11-02 12:34:56.123456', 'YYYY-MM-DD HH24:MI:= SS.FF' || i) FROM generate_series(1, 6) i; SELECT i, to_timestamp('2018-11-02 12:34:56.123456789', 'YYYY-MM-DD HH24:= MI:SS.FF' || i) FROM generate_series(1, 6) i; +SELECT i, to_timestamp('20181102123456123456', 'YYYYMMDDHH24MISSFF' || i)= FROM generate_series(1, 6) i; = SELECT to_date('1 4 1902', 'Q MM YYYY'); -- Q is ignored SELECT to_date('3 4 21 01', 'W MM CC YY'); ------- =_aaaaaaaaaa0--