postgres.git / summary / log / commit / refs
commit 9dde82899cdf48bd7b2f3d83e4f724ac9ae02c79
Author: Robert Haas <rhaas@postgresql.org>
Date: Mon Mar 14 20:46:46 2022 +0000
First released in: 15.0
Support "of", "tzh", and "tzm" format codes.
The upper case versions "OF", "TZH", and "TZM" are already supported,
and all other format codes that are supported in upper case are also
supported in lower case, so we should support these as well for
consistency.
Nitin Jadhav, with a tiny cosmetic change by me. Reviewed by Suraj
Kharage and David Zhang.
Discussion: http://postgr.es/m/CAMm1aWZ-oZyKd75+8D=VJ0sAoSwtdXWLP-MAWD4D8R1Dgandzw@mail.gmail.com
src/backend/utils/adt/formatting.c | 10 ++++-
src/test/regress/expected/timestamptz.out | 65 +++++++++++++++++++++++++++++++
src/test/regress/sql/timestamptz.sql | 22 +++++++++++
3 files changed, 95 insertions(+), 2 deletions(-)
diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c
index d4c2e7b0692..ed698f788de 100644
--- a/src/backend/utils/adt/formatting.c
+++ b/src/backend/utils/adt/formatting.c
@@ -702,6 +702,7 @@ typedef enum
DCH_month,
DCH_mon,
DCH_ms,
+ DCH_of,
DCH_p_m,
DCH_pm,
DCH_q,
@@ -709,6 +710,8 @@ typedef enum
DCH_sssss,
DCH_ssss,
DCH_ss,
+ DCH_tzh,
+ DCH_tzm,
DCH_tz,
DCH_us,
DCH_ww,
@@ -865,6 +868,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},
+ {"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},
{"q", 1, DCH_Q, true, FROM_CHAR_DATE_NONE}, /* q */
@@ -872,7 +876,9 @@ static const KeyWord DCH_keywords[] = {
{"sssss", 5, DCH_SSSS, true, FROM_CHAR_DATE_NONE}, /* s */
{"ssss", 4, DCH_SSSS, true, FROM_CHAR_DATE_NONE},
{"ss", 2, DCH_SS, true, FROM_CHAR_DATE_NONE},
- {"tz", 2, DCH_tz, false, FROM_CHAR_DATE_NONE}, /* t */
+ {"tzh", 3, DCH_TZH, false, FROM_CHAR_DATE_NONE}, /* t */
+ {"tzm", 3, DCH_TZM, true, FROM_CHAR_DATE_NONE},
+ {"tz", 2, DCH_tz, false, FROM_CHAR_DATE_NONE},
{"us", 2, DCH_US, true, FROM_CHAR_DATE_NONE}, /* u */
{"ww", 2, DCH_WW, true, FROM_CHAR_DATE_GREGORIAN}, /* w */
{"w", 1, DCH_W, true, FROM_CHAR_DATE_GREGORIAN},
@@ -954,7 +960,7 @@ static const int DCH_index[KeyWord_INDEX_SIZE] = {
DCH_P_M, DCH_Q, DCH_RM, DCH_SSSSS, DCH_TZH, DCH_US, -1, DCH_WW, -1, DCH_Y_YYY,
-1, -1, -1, -1, -1, -1, -1, DCH_a_d, DCH_b_c, DCH_cc,
DCH_day, -1, DCH_ff1, -1, DCH_hh24, DCH_iddd, DCH_j, -1, -1, DCH_mi,
- -1, -1, DCH_p_m, DCH_q, DCH_rm, DCH_sssss, DCH_tz, DCH_us, -1, DCH_ww,
+ -1, DCH_of, DCH_p_m, DCH_q, DCH_rm, DCH_sssss, DCH_tzh, DCH_us, -1, DCH_ww,
-1, DCH_y_yyy, -1, -1, -1, -1
/*---- chars over 126 are skipped ----*/
diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out
index 990c4eddf13..d402802db0d 100644
--- a/src/test/regress/expected/timestamptz.out
+++ b/src/test/regress/expected/timestamptz.out
@@ -2166,6 +2166,71 @@ SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM";
-04:15 | -04:15
(1 row)
+RESET timezone;
+-- Check of, tzh, tzm with various zone offsets.
+SET timezone = '00:00';
+SELECT to_char(now(), 'of') as "Of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
+ Of | tzh:tzm
+-----+---------
+ +00 | +00:00
+(1 row)
+
+SET timezone = '+02:00';
+SELECT to_char(now(), 'of') as "of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
+ of | tzh:tzm
+-----+---------
+ -02 | -02:00
+(1 row)
+
+SET timezone = '-13:00';
+SELECT to_char(now(), 'of') as "of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
+ of | tzh:tzm
+-----+---------
+ +13 | +13:00
+(1 row)
+
+SET timezone = '-00:30';
+SELECT to_char(now(), 'of') as "of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
+ of | tzh:tzm
+--------+---------
+ +00:30 | +00:30
+(1 row)
+
+SET timezone = '00:30';
+SELECT to_char(now(), 'of') as "of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
+ of | tzh:tzm
+--------+---------
+ -00:30 | -00:30
+(1 row)
+
+SET timezone = '-04:30';
+SELECT to_char(now(), 'of') as "of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
+ of | tzh:tzm
+--------+---------
+ +04:30 | +04:30
+(1 row)
+
+SET timezone = '04:30';
+SELECT to_char(now(), 'of') as "of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
+ of | tzh:tzm
+--------+---------
+ -04:30 | -04:30
+(1 row)
+
+SET timezone = '-04:15';
+SELECT to_char(now(), 'of') as "of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
+ of | tzh:tzm
+--------+---------
+ +04:15 | +04:15
+(1 row)
+
+SET timezone = '04:15';
+SELECT to_char(now(), 'of') as "of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
+ of | tzh:tzm
+--------+---------
+ -04:15 | -04:15
+(1 row)
+
RESET timezone;
CREATE TABLE TIMESTAMPTZ_TST (a int , b timestamptz);
-- Test year field value with len > 4
diff --git a/src/test/regress/sql/timestamptz.sql b/src/test/regress/sql/timestamptz.sql
index b18821de53b..7cd2420b080 100644
--- a/src/test/regress/sql/timestamptz.sql
+++ b/src/test/regress/sql/timestamptz.sql
@@ -360,6 +360,28 @@ SET timezone = '04:15';
SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM";
RESET timezone;
+-- Check of, tzh, tzm with various zone offsets.
+SET timezone = '00:00';
+SELECT to_char(now(), 'of') as "Of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
+SET timezone = '+02:00';
+SELECT to_char(now(), 'of') as "of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
+SET timezone = '-13:00';
+SELECT to_char(now(), 'of') as "of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
+SET timezone = '-00:30';
+SELECT to_char(now(), 'of') as "of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
+SET timezone = '00:30';
+SELECT to_char(now(), 'of') as "of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
+SET timezone = '-04:30';
+SELECT to_char(now(), 'of') as "of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
+SET timezone = '04:30';
+SELECT to_char(now(), 'of') as "of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
+SET timezone = '-04:15';
+SELECT to_char(now(), 'of') as "of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
+SET timezone = '04:15';
+SELECT to_char(now(), 'of') as "of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
+RESET timezone;
+
+
CREATE TABLE TIMESTAMPTZ_TST (a int , b timestamptz);
-- Test year field value with len > 4
[parent: 705e20f8550c]