Re: FF3 vs MS Date/Time Template - ERROR: date/time field value out of range

Nick Davies <Nick.Davies@iliad-solutions.com> writes:
> In the docs it appears the 'MS' and 'FF3' date time template patterns are identical but the behaviour is different.
Wealso support Oracle which doesn't have 'MS', so we tried to use the pattern 'YYYYMMDDHH24MISSFF3'. This doesn't work,
butswitching to 'MS' does. 

Thanks for the report!  The problem seems to be that the FFn codes are
mislabeled with is_digit = false.  That causes the preceding SS code
to think that it should eat all the remaining digits, so we end with
seconds = 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

diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.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[] = {
     {"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[] = {
     {"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/expected/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.123456789"
+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/horology.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', 'YYYY-MM-DD HH24:MI:SS.FF' ||
 SELECT i, to_timestamp('2018-11-02 12:34:56.12345', 'YYYY-MM-DD HH24:MI:SS.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');