pg.ddx.io pgsql-bugs@postgresql.org mailing list archive help / color / mirror / Atom feed From: Tom Lane <tgl@sss.pgh.pa.us>
To: Nick Davies <Nick.Davies@iliad-solutions.com>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: FF3 vs MS Date/Time Template - ERROR: date/time field value out of range
Date: Sat, 07 Dec 2024 12:49:45 -0500
Message-ID: <3281202.1733593785@sss.pgh.pa.us> (raw )
In-Reply-To: <AM8PR08MB6356AC979252CFEA78B56678B6312@AM8PR08MB6356.eurprd08.prod.outlook.com >
References: <AM8PR08MB6356AC979252CFEA78B56678B6312@AM8PR08MB6356.eurprd08.prod.outlook.com >
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. We also support Oracle which doesn't have 'MS', so we tried to use the pattern 'YYYYMMDDHH24MISSFF3'. This 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 = 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
Attachments:
fix-FF-is_digit-labeling.patch (text/x-diff, 4.2K) download
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');
view thread (3+ messages) latest in thread
Message-ID: <3281202.1733593785@sss.pgh.pa.us>
Permalink: ../3281202.1733593785@sss.pgh.pa.us/
Also on: postgresql.org/message-id/3281202.1733593785@sss.pgh.pa.us
copy link · copy postgr.es · copy git am
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, Nick.Davies@iliad-solutions.com, pgsql-bugs@lists.postgresql.org
Subject: Re: FF3 vs MS Date/Time Template - ERROR: date/time field value out of range
In-Reply-To: <3281202.1733593785@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox