pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedFF3 vs MS Date/Time Template - ERROR: date/time field value out of range
3+ messages / 2 participants
[nested] [flat]
Commits from this thread:
2024-12-07 3220ceaf77 f2eba400bc 765f76d8cd 26c233b8b8 d2f59497a3 5882a4ba09 Fix is_digit labeling of to_timestamp's FFn format codes.
* FF3 vs MS Date/Time Template - ERROR: date/time field value out of range
@ 2024-12-06 12:44 Nick Davies <Nick.Davies@iliad-solutions.com>
0 siblings, 1 reply; 3+ messages in thread
From: Nick Davies @ 2024-12-06 12:44 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Hi,
We have some legacy tables with timestamps in an unusual String format - year, month, day, hour, minute, second, millisecond with no separators.
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.
-------------------------------------------------------------------------------------
psql (17.2 (Debian 17.2-1.pgdg120+1))
Type "help" for help.
postgres=# select to_timestamp('20241206111433123', 'YYYYMMDDHH24MISSFF3');
ERROR: date/time field value out of range: "20241206111433123"
postgres=# select to_timestamp('20241206111433123', 'YYYYMMDDHH24MISSMS');
to_timestamp
----------------------------
2024-12-06 11:14:33.123+00
(1 row)
-------------------------------------------------------------------------------------
Other observations:
*
I'd expect these patterns to work the same way - or at least to be documented differently.
*
FF3 works fine if there's a separator in the string, i.e. select to_timestamp('20241206111433.123', 'YYYYMMDDHH24MISS.FF3');
*
The other FF<n> patterns behave the same way.
*
I've reproduced this on Postgres 17.2 on Linux/x86_64 and Linux/ARM64, and also Postgres 13.18 on Linux/ARM64
Thanks very much,
Nick Davies
This message is private and confidential. It must not be distributed without our consent. If you have received this message in error, please notify us immediately, remove it from your system and do not disclose, distribute or retain this e-mail or any part of it. Unless expressly stated, opinions in this e-mail are those of the individual author and not those of Iliad Solutions , its directors or officers or any of its subsidiaries. You must take full responsibility for virus checking this e-mail and any attachments. Please note that the content of this e-mail or any of its attachments may also contain personal data that falls within the scope of the Data Protection Act 2018 and the General Data Protection Regulation. You must ensure that any handling or processing of such data by you is fully compliant with these legal and regulatory requirements.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: FF3 vs MS Date/Time Template - ERROR: date/time field value out of range
@ 2024-12-07 17:49 Tom Lane <tgl@sss.pgh.pa.us>
parent: Nick Davies <Nick.Davies@iliad-solutions.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2024-12-07 17:49 UTC (permalink / raw)
To: Nick Davies <Nick.Davies@iliad-solutions.com>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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');
* Re: FF3 vs MS Date/Time Template - ERROR: date/time field value out of range
@ 2024-12-09 09:16 Nick Davies <Nick.Davies@iliad-solutions.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Nick Davies @ 2024-12-09 09:16 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Thank you so much, that's a seriously speedy turnaround.
Regards
Nick
________________________________
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: 07 December 2024 17:49
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
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
This message is private and confidential. It must not be distributed without our consent. If you have received this message in error, please notify us immediately, remove it from your system and do not disclose, distribute or retain this e-mail or any part of it. Unless expressly stated, opinions in this e-mail are those of the individual author and not those of Iliad Solutions , its directors or officers or any of its subsidiaries. You must take full responsibility for virus checking this e-mail and any attachments. Please note that the content of this e-mail or any of its attachments may also contain personal data that falls within the scope of the Data Protection Act 2018 and the General Data Protection Regulation. You must ensure that any handling or processing of such data by you is fully compliant with these legal and regulatory requirements.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-12-09 09:16 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-12-06 12:44 FF3 vs MS Date/Time Template - ERROR: date/time field value out of range Nick Davies <Nick.Davies@iliad-solutions.com>
2024-12-07 17:49 ` Re: FF3 vs MS Date/Time Template - ERROR: date/time field value out of range Tom Lane <tgl@sss.pgh.pa.us>
2024-12-09 09:16 ` Re: FF3 vs MS Date/Time Template - ERROR: date/time field value out of range Nick Davies <Nick.Davies@iliad-solutions.com>
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