[PATCH] Add a new pattern for zero-based months for Date/Time Formatting
Started by Vincent Moreau10 months ago1 messages
Hi,
I came across date information from an external data source where the
month number is zero-based (January = 0, December = 11) and found that
I couldn't process it directly using the TO_DATE function.
This patch introduces a new pattern (MZ) for handling zero-based
months in Date/Time Formatting.
## Example
SELECT to_date('01012025', 'DDMZYYYY');
to_date
------------
2025-02-01
## Implementation notes
This is my first patch submission. I have tried to follow the
guidelines from various documents, but please let me know if anything
is missing or not aligned with expectations. My apologies in advance
for any oversights.
Thanks,
Vincent Moreau
Attachments:
Add-mz-pattern.patchapplication/octet-stream; name=Add-mz-pattern.patchDownload
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index d8b27903593..7fd6b49d1f2 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -8085,6 +8085,10 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
<entry><literal>MM</literal></entry>
<entry>month number (01–12)</entry>
</row>
+ <row>
+ <entry><literal>MZ</literal></entry>
+ <entry>month number (zero–based) (00–11)</entry>
+ </row>
<row>
<entry><literal>DAY</literal></entry>
<entry>full upper case day name (blank-padded to 9 chars)</entry>
diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c
index 5f4b37abad2..484fbc6e17f 100644
--- a/src/backend/utils/adt/formatting.c
+++ b/src/backend/utils/adt/formatting.c
@@ -641,6 +641,7 @@ typedef enum
DCH_I,
DCH_J,
DCH_MI,
+ DCH_MZ,
DCH_MM,
DCH_MONTH,
DCH_MON,
@@ -813,6 +814,7 @@ static const KeyWord DCH_keywords[] = {
{"MS", 2, DCH_MS, true, FROM_CHAR_DATE_NONE},
{"Month", 5, DCH_Month, false, FROM_CHAR_DATE_GREGORIAN},
{"Mon", 3, DCH_Mon, false, FROM_CHAR_DATE_GREGORIAN},
+ {"MZ", 2, DCH_MZ, true, FROM_CHAR_DATE_GREGORIAN},
{"OF", 2, DCH_OF, false, FROM_CHAR_DATE_NONE}, /* O */
{"P.M.", 4, DCH_P_M, false, FROM_CHAR_DATE_NONE}, /* P */
{"PM", 2, DCH_PM, false, FROM_CHAR_DATE_NONE},
@@ -867,6 +869,7 @@ static const KeyWord DCH_keywords[] = {
{"month", 5, DCH_month, false, FROM_CHAR_DATE_GREGORIAN},
{"mon", 3, DCH_mon, false, FROM_CHAR_DATE_GREGORIAN},
{"ms", 2, DCH_MS, true, FROM_CHAR_DATE_NONE},
+ {"mz", 2, DCH_MZ, true, FROM_CHAR_DATE_GREGORIAN},
{"of", 2, DCH_OF, false, FROM_CHAR_DATE_NONE}, /* o */
{"p.m.", 4, DCH_p_m, false, FROM_CHAR_DATE_NONE}, /* p */
{"pm", 2, DCH_pm, false, FROM_CHAR_DATE_NONE},
@@ -3076,6 +3079,13 @@ DCH_to_char(FormatNode *node, bool is_interval, TmToChar *in, char *out, Oid col
str_numth(s, s, S_TH_TYPE(n->suffix));
s += strlen(s);
break;
+ case DCH_MZ:
+ sprintf(s, "%0*d", S_FM(n->suffix) ? 0 : (tm->tm_mon >= 0) ? 2 : 3,
+ tm->tm_mon - 1);
+ if (S_THth(n->suffix))
+ str_numth(s, s, S_TH_TYPE(n->suffix));
+ s += strlen(s);
+ break;
case DCH_DAY:
INVALID_FOR_INTERVAL;
if (S_TM(n->suffix))
@@ -3760,6 +3770,18 @@ DCH_from_char(FormatNode *node, const char *in, TmFromChar *out,
return;
SKIP_THth(s, n->suffix);
break;
+ case DCH_MZ:
+ if (from_char_parse_int(&out->mm, &s, n, escontext) < 0)
+ return;
+ if (out->mm >= 0 && out->mm <= 11) {
+ out->mm += 1; // Convert (0-11) to (1-12)
+ }
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_DATETIME_FORMAT),
+ errmsg("Invalid month value for MZ: must be between 0 and 11")));
+ SKIP_THth(s, n->suffix);
+ break;
case DCH_DAY:
case DCH_Day:
case DCH_day:
@@ -4032,6 +4054,7 @@ DCH_datetime_type(FormatNode *node)
case DCH_MON:
case DCH_Mon:
case DCH_mon:
+ case DCH_MZ:
case DCH_MM:
case DCH_DAY:
case DCH_Day:
diff --git a/src/test/regress/expected/horology.out b/src/test/regress/expected/horology.out
index 12eefb09d4d..a6a5d3bef63 100644
--- a/src/test/regress/expected/horology.out
+++ b/src/test/regress/expected/horology.out
@@ -3482,6 +3482,14 @@ SELECT to_date('2458872', 'J');
01-23-2020
(1 row)
+SELECT to_date('01012025', 'DDMZYYYY');
+ to_date
+------------
+ 02-01-2025
+(1 row)
+
+SELECT to_date('01122025', 'DDMZYYYY'); -- error
+ERROR: Invalid month value for MZ: must be between 0 and 11
--
-- Check handling of BC dates
--
diff --git a/src/test/regress/sql/horology.sql b/src/test/regress/sql/horology.sql
index 86481637223..1a37c004882 100644
--- a/src/test/regress/sql/horology.sql
+++ b/src/test/regress/sql/horology.sql
@@ -564,6 +564,9 @@ SELECT to_date('1 4 1902', 'Q MM YYYY'); -- Q is ignored
SELECT to_date('3 4 21 01', 'W MM CC YY');
SELECT to_date('2458872', 'J');
+SELECT to_date('01012025', 'DDMZYYYY');
+SELECT to_date('01122025', 'DDMZYYYY'); -- error
+
--
-- Check handling of BC dates
--