Query about time zone patterns in to_char

Started by Nitin Jadhavover 4 years ago14 messages

nitinjadhavpostgres@gmail.com

over 4 years ago

Hi Hackers,

While understanding the behaviour of the to_char() function as
explained in [1]https://www.postgresql.org/docs/current/functions-formatting.html, I observed that some patterns related to time zones
do not display values if we mention in lower case. As shown in the
sample output [2]postgres@123613=#select to_char(current_timestamp, 'TZH'); to_char --------- +05 (1 row), time zone related patterns TZH, TZM and OF outputs
proper values when specified in upper case but does not work if we
mention in lower case. But other patterns like TZ, HH, etc works fine
with upper case as well as lower case.

I would like to know whether the current behaviour of TZH, TZM and OF
is done intentionally and is as expected.
Please share your thoughts.

[1]: https://www.postgresql.org/docs/current/functions-formatting.html

[2]: postgres@123613=#select to_char(current_timestamp, 'TZH'); to_char --------- +05 (1 row)
postgres@123613=#select to_char(current_timestamp, 'TZH');
to_char
---------
+05
(1 row)

postgres@123613=#select to_char(current_timestamp, 'TZM');
to_char
---------
30
(1 row)

postgres@123613=#select to_char(current_timestamp, 'OF');
to_char
---------
+05:30
(1 row)

postgres@123613=#select to_char(current_timestamp, 'tzh');
to_char
---------
isth
(1 row)

postgres@123613=#select to_char(current_timestamp, 'tzm');
to_char
---------
istm
(1 row)

postgres@123613=#select to_char(current_timestamp, 'of');
to_char
---------
of
(1 row)

[3]: postgres@123613=#select to_char(current_timestamp, 'tz'); to_char --------- ist (1 row)
postgres@123613=#select to_char(current_timestamp, 'tz');
to_char
---------
ist
(1 row)

postgres@123613=#select to_char(current_timestamp, 'TZ');
to_char
---------
IST
(1 row)

postgres@123613=#select to_char(current_timestamp, 'HH');
to_char
---------
08
(1 row)

postgres@123613=#select to_char(current_timestamp, 'hh');
to_char
---------
08
(1 row)

Thanks & Regards,
Nitin Jadhav

Tom Lane

tgl@sss.pgh.pa.us

over 4 years ago

In reply to: Nitin Jadhav (#1)

Re: Query about time zone patterns in to_char

Nitin Jadhav <nitinjadhavpostgres@gmail.com> writes:

While understanding the behaviour of the to_char() function as
explained in [1], I observed that some patterns related to time zones
do not display values if we mention in lower case. As shown in the
sample output [2], time zone related patterns TZH, TZM and OF outputs
proper values when specified in upper case but does not work if we
mention in lower case. But other patterns like TZ, HH, etc works fine
with upper case as well as lower case.

I would like to know whether the current behaviour of TZH, TZM and OF
is done intentionally and is as expected.

AFAICS, table 9.26 specifically shows which case-variants are supported.
If there are some others that happen to work, we probably shouldn't
remove them for fear of breaking poorly-written apps ... but that does
not imply that we need to support every case-variant.

regards, tom lane

Nitin Jadhav

nitinjadhavpostgres@gmail.com

over 4 years ago

In reply to: Tom Lane (#2)

Re: Query about time zone patterns in to_char

AFAICS, table 9.26 specifically shows which case-variants are supported.
If there are some others that happen to work, we probably shouldn't
remove them for fear of breaking poorly-written apps ... but that does
not imply that we need to support every case-variant.

Thanks for the explanation. I also feel that we may not support every
case-variant. But the other reason which triggered me to think in the
other way is, as mentioned in commit [1]commit 11b623dd0a2c385719ebbbdd42dd4ec395dcdc9d Author: Andrew Dunstan <andrew@dunslane.net> Date: Tue Jan 9 14:25:05 2018 -0500 where this feature was added,
says that these format patterns are compatible with Oracle. Whereas
Oracle supports both upper case and lower case patterns. I just wanted
to get it confirmed with this point before concluding.

[1]: commit 11b623dd0a2c385719ebbbdd42dd4ec395dcdc9d Author: Andrew Dunstan <andrew@dunslane.net> Date: Tue Jan 9 14:25:05 2018 -0500
commit 11b623dd0a2c385719ebbbdd42dd4ec395dcdc9d
Author: Andrew Dunstan <andrew@dunslane.net>
Date: Tue Jan 9 14:25:05 2018 -0500

Implement TZH and TZM timestamp format patterns

These are compatible with Oracle and required for the datetime template
language for jsonpath in an upcoming patch.

Nikita Glukhov and Andrew Dunstan, reviewed by Pavel Stehule.

Thanks & Regards,
Nitin Jadhav

Show quoted text

On Sun, May 16, 2021 at 8:40 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Nitin Jadhav <nitinjadhavpostgres@gmail.com> writes:

While understanding the behaviour of the to_char() function as
explained in [1], I observed that some patterns related to time zones
do not display values if we mention in lower case. As shown in the
sample output [2], time zone related patterns TZH, TZM and OF outputs
proper values when specified in upper case but does not work if we
mention in lower case. But other patterns like TZ, HH, etc works fine
with upper case as well as lower case.

I would like to know whether the current behaviour of TZH, TZM and OF
is done intentionally and is as expected.

AFAICS, table 9.26 specifically shows which case-variants are supported.
If there are some others that happen to work, we probably shouldn't
remove them for fear of breaking poorly-written apps ... but that does
not imply that we need to support every case-variant.

regards, tom lane

Tom Lane

tgl@sss.pgh.pa.us

over 4 years ago

In reply to: Nitin Jadhav (#3)

Re: Query about time zone patterns in to_char

Nitin Jadhav <nitinjadhavpostgres@gmail.com> writes:

Thanks for the explanation. I also feel that we may not support every
case-variant. But the other reason which triggered me to think in the
other way is, as mentioned in commit [1] where this feature was added,
says that these format patterns are compatible with Oracle. Whereas
Oracle supports both upper case and lower case patterns. I just wanted
to get it confirmed with this point before concluding.

Hm. If Oracle does that, then there's an argument for us doing it
too. I can't get hugely excited about it, but maybe someone else
cares enough to prepare a patch.

regards, tom lane

Nitin Jadhav

nitinjadhavpostgres@gmail.com

over 4 years ago

In reply to: Tom Lane (#4)

1 attachment(s)

Re: Query about time zone patterns in to_char

Hm. If Oracle does that, then there's an argument for us doing it
too. I can't get hugely excited about it, but maybe someone else
cares enough to prepare a patch.

Thanks for the confirmation. Attached patch supports these format
patterns. Kindly review and let me know if any changes are required.

Thanks & Regards,
Nitin Jadhav

Show quoted text

On Sun, May 16, 2021 at 10:34 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Nitin Jadhav <nitinjadhavpostgres@gmail.com> writes:

Thanks for the explanation. I also feel that we may not support every
case-variant. But the other reason which triggered me to think in the
other way is, as mentioned in commit [1] where this feature was added,
says that these format patterns are compatible with Oracle. Whereas
Oracle supports both upper case and lower case patterns. I just wanted
to get it confirmed with this point before concluding.

Hm. If Oracle does that, then there's an argument for us doing it
too. I can't get hugely excited about it, but maybe someone else
cares enough to prepare a patch.

regards, tom lane

Attachments:

v1_support_of_tzh_tzm_patters.patchapplication/octet-stream; name=v1_support_of_tzh_tzm_patters.patchDownload

diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c
index a1145e2..ddafe9a 100644
--- a/src/backend/utils/adt/formatting.c
+++ b/src/backend/utils/adt/formatting.c
@@ -703,6 +703,7 @@ typedef enum
 	DCH_month,
 	DCH_mon,
 	DCH_ms,
+	DCH_of,
 	DCH_p_m,
 	DCH_pm,
 	DCH_q,
@@ -710,6 +711,8 @@ typedef enum
 	DCH_sssss,
 	DCH_ssss,
 	DCH_ss,
+	DCH_tzh,
+	DCH_tzm,
 	DCH_tz,
 	DCH_us,
 	DCH_ww,
@@ -866,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},
+	{"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 */
@@ -873,6 +877,8 @@ 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},
+	{"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},	/* t */
 	{"us", 2, DCH_US, true, FROM_CHAR_DATE_NONE},	/* u */
 	{"ww", 2, DCH_WW, true, FROM_CHAR_DATE_GREGORIAN},	/* w */
@@ -955,7 +961,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 421ef25..e7f56b9 100644
--- a/src/test/regress/expected/timestamptz.out
+++ b/src/test/regress/expected/timestamptz.out
@@ -2161,6 +2161,71 @@ SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM";
 (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
 INSERT INTO TIMESTAMPTZ_TST VALUES(1, 'Sat Mar 12 23:58:48 1000 IST');
diff --git a/src/test/regress/sql/timestamptz.sql b/src/test/regress/sql/timestamptz.sql
index 17ced99..a79a9da 100644
--- a/src/test/regress/sql/timestamptz.sql
+++ b/src/test/regress/sql/timestamptz.sql
@@ -354,6 +354,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

David Rowley

dgrowleyml@gmail.com

over 4 years ago

In reply to: Nitin Jadhav (#5)

Re: Query about time zone patterns in to_char

On Mon, 17 May 2021 at 06:23, Nitin Jadhav
<nitinjadhavpostgres@gmail.com> wrote:

Hm. If Oracle does that, then there's an argument for us doing it
too. I can't get hugely excited about it, but maybe someone else
cares enough to prepare a patch.

Thanks for the confirmation. Attached patch supports these format
patterns. Kindly review and let me know if any changes are required.

Please add it to the July commitfest: https://commitfest.postgresql.org/33/

David

Nitin Jadhav

nitinjadhavpostgres@gmail.com

over 4 years ago

In reply to: David Rowley (#6)

Re: Query about time zone patterns in to_char

Please add it to the July commitfest: https://commitfest.postgresql.org/33/

Added a commitfest entry https://commitfest.postgresql.org/33/3121/

Thanks & Regards,
Nitin Jadhav

Show quoted text

On Mon, May 17, 2021 at 7:05 AM David Rowley <dgrowleyml@gmail.com> wrote:

On Mon, 17 May 2021 at 06:23, Nitin Jadhav
<nitinjadhavpostgres@gmail.com> wrote:

Hm. If Oracle does that, then there's an argument for us doing it
too. I can't get hugely excited about it, but maybe someone else
cares enough to prepare a patch.

Thanks for the confirmation. Attached patch supports these format
patterns. Kindly review and let me know if any changes are required.

Please add it to the July commitfest: https://commitfest.postgresql.org/33/

David

Suraj Kharage

suraj.kharage@enterprisedb.com

over 4 years ago

In reply to: Nitin Jadhav (#3)

Re: Query about time zone patterns in to_char

+1 for the change.

I quickly reviewed the patch and overall it looks good to me.
Few cosmetic suggestions:

1:
+RESET timezone;
+
+
 CREATE TABLE TIMESTAMPTZ_TST (a int , b timestamptz);

Extra line.

2:
+SET timezone = '00:00';
+SELECT to_char(now(), 'of') as "Of", to_char(now(), 'tzh:tzm') as
"tzh:tzm";

O should be small in alias just for consistency.

I am not sure whether we should backport this or not but I don't see any
issues with back-patching.

On Sun, May 16, 2021 at 9:43 PM Nitin Jadhav <nitinjadhavpostgres@gmail.com>
wrote:

AFAICS, table 9.26 specifically shows which case-variants are supported.
If there are some others that happen to work, we probably shouldn't
remove them for fear of breaking poorly-written apps ... but that does
not imply that we need to support every case-variant.

Thanks for the explanation. I also feel that we may not support every
case-variant. But the other reason which triggered me to think in the
other way is, as mentioned in commit [1] where this feature was added,
says that these format patterns are compatible with Oracle. Whereas
Oracle supports both upper case and lower case patterns. I just wanted
to get it confirmed with this point before concluding.

[1] -
commit 11b623dd0a2c385719ebbbdd42dd4ec395dcdc9d
Author: Andrew Dunstan <andrew@dunslane.net>
Date: Tue Jan 9 14:25:05 2018 -0500

Implement TZH and TZM timestamp format patterns

These are compatible with Oracle and required for the datetime template
language for jsonpath in an upcoming patch.

Nikita Glukhov and Andrew Dunstan, reviewed by Pavel Stehule.

Thanks & Regards,
Nitin Jadhav

On Sun, May 16, 2021 at 8:40 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Nitin Jadhav <nitinjadhavpostgres@gmail.com> writes:

While understanding the behaviour of the to_char() function as
explained in [1], I observed that some patterns related to time zones
do not display values if we mention in lower case. As shown in the
sample output [2], time zone related patterns TZH, TZM and OF outputs
proper values when specified in upper case but does not work if we
mention in lower case. But other patterns like TZ, HH, etc works fine
with upper case as well as lower case.

I would like to know whether the current behaviour of TZH, TZM and OF
is done intentionally and is as expected.

AFAICS, table 9.26 specifically shows which case-variants are supported.
If there are some others that happen to work, we probably shouldn't
remove them for fear of breaking poorly-written apps ... but that does
not imply that we need to support every case-variant.

regards, tom lane

--
--

Thanks & Regards,
Suraj kharage,

edbpostgres.com

Nitin Jadhav

nitinjadhavpostgres@gmail.com

over 4 years ago

In reply to: Suraj Kharage (#8)

1 attachment(s)

Re: Query about time zone patterns in to_char

Thanks Suraj for reviewing the patch.

1:
+RESET timezone;
+
+
CREATE TABLE TIMESTAMPTZ_TST (a int , b timestamptz);

Extra line.

2:
+SET timezone = '00:00';
+SELECT to_char(now(), 'of') as "Of", to_char(now(), 'tzh:tzm') as

"tzh:tzm";

I have fixed these comments.

I am not sure whether we should backport this or not but I don't see any

issues with back-patching.

I am also not sure about this. If it is really required, I would like to
create those patches.

Please find the patch attached. Kindly confirm and share comments if any.

--
Thanks & Regards,
Nitin Jadhav

On Thu, May 20, 2021 at 8:55 AM Suraj Kharage <
suraj.kharage@enterprisedb.com> wrote:

Show quoted text

+1 for the change.

I quickly reviewed the patch and overall it looks good to me.
Few cosmetic suggestions:
1:
+RESET timezone;
+
+
CREATE TABLE TIMESTAMPTZ_TST (a int , b timestamptz);
Extra line.
2:
+SET timezone = '00:00';
+SELECT to_char(now(), 'of') as "Of", to_char(now(), 'tzh:tzm') as
"tzh:tzm";
O should be small in alias just for consistency.

I am not sure whether we should backport this or not but I don't see any
issues with back-patching.

On Sun, May 16, 2021 at 9:43 PM Nitin Jadhav <
nitinjadhavpostgres@gmail.com> wrote:

AFAICS, table 9.26 specifically shows which case-variants are supported.
If there are some others that happen to work, we probably shouldn't
remove them for fear of breaking poorly-written apps ... but that does
not imply that we need to support every case-variant.

Thanks for the explanation. I also feel that we may not support every
case-variant. But the other reason which triggered me to think in the
other way is, as mentioned in commit [1] where this feature was added,
says that these format patterns are compatible with Oracle. Whereas
Oracle supports both upper case and lower case patterns. I just wanted
to get it confirmed with this point before concluding.

[1] -
commit 11b623dd0a2c385719ebbbdd42dd4ec395dcdc9d
Author: Andrew Dunstan <andrew@dunslane.net>
Date: Tue Jan 9 14:25:05 2018 -0500

Implement TZH and TZM timestamp format patterns

These are compatible with Oracle and required for the datetime
template
language for jsonpath in an upcoming patch.

Nikita Glukhov and Andrew Dunstan, reviewed by Pavel Stehule.

Thanks & Regards,
Nitin Jadhav

On Sun, May 16, 2021 at 8:40 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Nitin Jadhav <nitinjadhavpostgres@gmail.com> writes:

While understanding the behaviour of the to_char() function as
explained in [1], I observed that some patterns related to time zones
do not display values if we mention in lower case. As shown in the
sample output [2], time zone related patterns TZH, TZM and OF outputs
proper values when specified in upper case but does not work if we
mention in lower case. But other patterns like TZ, HH, etc works fine
with upper case as well as lower case.

I would like to know whether the current behaviour of TZH, TZM and OF
is done intentionally and is as expected.

AFAICS, table 9.26 specifically shows which case-variants are supported.
If there are some others that happen to work, we probably shouldn't
remove them for fear of breaking poorly-written apps ... but that does
not imply that we need to support every case-variant.

regards, tom lane

--
--

Thanks & Regards,
Suraj kharage,

edbpostgres.com

Attachments:

v2_support_of_tzh_tzm_patterns.patchapplication/octet-stream; name=v2_support_of_tzh_tzm_patterns.patchDownload

diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c
index a1145e2..ddafe9a 100644
--- a/src/backend/utils/adt/formatting.c
+++ b/src/backend/utils/adt/formatting.c
@@ -703,6 +703,7 @@ typedef enum
 	DCH_month,
 	DCH_mon,
 	DCH_ms,
+	DCH_of,
 	DCH_p_m,
 	DCH_pm,
 	DCH_q,
@@ -710,6 +711,8 @@ typedef enum
 	DCH_sssss,
 	DCH_ssss,
 	DCH_ss,
+	DCH_tzh,
+	DCH_tzm,
 	DCH_tz,
 	DCH_us,
 	DCH_ww,
@@ -866,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},
+	{"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 */
@@ -873,6 +877,8 @@ 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},
+	{"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},	/* t */
 	{"us", 2, DCH_US, true, FROM_CHAR_DATE_NONE},	/* u */
 	{"ww", 2, DCH_WW, true, FROM_CHAR_DATE_GREGORIAN},	/* w */
@@ -955,7 +961,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 421ef25..2702e87 100644
--- a/src/test/regress/expected/timestamptz.out
+++ b/src/test/regress/expected/timestamptz.out
@@ -2161,6 +2161,71 @@ SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM";
 (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
 INSERT INTO TIMESTAMPTZ_TST VALUES(1, 'Sat Mar 12 23:58:48 1000 IST');
diff --git a/src/test/regress/sql/timestamptz.sql b/src/test/regress/sql/timestamptz.sql
index 17ced99..f7664e2 100644
--- a/src/test/regress/sql/timestamptz.sql
+++ b/src/test/regress/sql/timestamptz.sql
@@ -354,6 +354,27 @@ 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

#10

Bruce Momjian

bruce@momjian.us

over 4 years ago

In reply to: Nitin Jadhav (#9)

Re: Query about time zone patterns in to_char

On Thu, May 20, 2021 at 12:21:12PM +0530, Nitin Jadhav wrote:

Thanks Suraj for reviewing the patch.
1:
+RESET timezone;
+
+
CREATE TABLE TIMESTAMPTZ_TST (a int , b timestamptz);
Extra line.
2:
+SET timezone = '00:00';
+SELECT to_char(now(), 'of') as "Of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
I have fixed these comments.

I am not sure whether we should backport this or not but I don't see any

issues with back-patching.

Only significant fixes are backpatched, not features.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

If only the physical world exists, free will is an illusion.

#11

Tomas Vondra

tomas.vondra@enterprisedb.com

over 4 years ago

In reply to: Bruce Momjian (#10)

Re: Query about time zone patterns in to_char

On 5/20/21 8:25 PM, Bruce Momjian wrote:

On Thu, May 20, 2021 at 12:21:12PM +0530, Nitin Jadhav wrote:
Thanks Suraj for reviewing the patch.
1:
+RESET timezone;
+
+
CREATE TABLE TIMESTAMPTZ_TST (a int , b timestamptz);
Extra line.
2:
+SET timezone = '00:00';
+SELECT to_char(now(), 'of') as "Of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
I have fixed these comments.

I am not sure whether we should backport this or not but I don't see any

issues with back-patching.
Only significant fixes are backpatched, not features.

Yeah, does not seem to be worth it, as there seem to be no actual
reports of issues in the field.

FWIW there seem to be quite a bit of other to_char differences compared
to Oracle (judging by docs and playing with sqlfiddle). But the patch
seems fine / simple enough and non-problematic, so perhaps let's just
get it committed?

regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

#12

David Zhang

david.zhang@highgo.ca

over 4 years ago

In reply to: Tomas Vondra (#11)

Re: Query about time zone patterns in to_char

The following review has been posted through the commitfest application:
make installcheck-world: not tested
Implements feature: tested, passed
Spec compliant: not tested
Documentation: not tested

Applied the patch `v2_support_of_tzh_tzm_patterns.patch` to `REL_14_STABLE` branch, both `make check` and `make check-world` are all passed.

#13

Robert Haas

robertmhaas@gmail.com

almost 4 years ago

In reply to: Tomas Vondra (#11)

Re: Query about time zone patterns in to_char

On Fri, Jul 9, 2021 at 10:44 AM Tomas Vondra
<tomas.vondra@enterprisedb.com> wrote:

Yeah, does not seem to be worth it, as there seem to be no actual
reports of issues in the field.

FWIW there seem to be quite a bit of other to_char differences compared
to Oracle (judging by docs and playing with sqlfiddle). But the patch
seems fine / simple enough and non-problematic, so perhaps let's just
get it committed?

This patch is still in the current CommitFest, so I decided to review
it. I see that DCH_keywords[] includes upper and lower-case entries
for everything except the three cases corrected by this patch, where
it includes upper-case entries but not the corresponding lower-case
entries. It seems to make sense to make these three cases consistent
with everything else.

It took me a while to understand how DCH_keywords[] and DCH_index[]
actually work, and I think it's a pretty confusing design, but what
the patch does seems to be consistent with that, so it appears correct
to me.

Therefore, I have committed it.

--
Robert Haas
EDB: http://www.enterprisedb.com

#14

Nitin Jadhav

nitinjadhavpostgres@gmail.com

almost 4 years ago

In reply to: Robert Haas (#13)

Re: Query about time zone patterns in to_char

This patch is still in the current CommitFest, so I decided to review
it. I see that DCH_keywords[] includes upper and lower-case entries
for everything except the three cases corrected by this patch, where
it includes upper-case entries but not the corresponding lower-case
entries. It seems to make sense to make these three cases consistent
with everything else.

It took me a while to understand how DCH_keywords[] and DCH_index[]
actually work, and I think it's a pretty confusing design, but what
the patch does seems to be consistent with that, so it appears correct
to me.

Therefore, I have committed it.

Thank you so much.

Thanks & Regards,
Nitin Jadhav

Show quoted text

On Tue, Mar 15, 2022 at 2:22 AM Robert Haas <robertmhaas@gmail.com> wrote:

On Fri, Jul 9, 2021 at 10:44 AM Tomas Vondra
<tomas.vondra@enterprisedb.com> wrote:

Yeah, does not seem to be worth it, as there seem to be no actual
reports of issues in the field.

FWIW there seem to be quite a bit of other to_char differences compared
to Oracle (judging by docs and playing with sqlfiddle). But the patch
seems fine / simple enough and non-problematic, so perhaps let's just
get it committed?

This patch is still in the current CommitFest, so I decided to review
it. I see that DCH_keywords[] includes upper and lower-case entries
for everything except the three cases corrected by this patch, where
it includes upper-case entries but not the corresponding lower-case
entries. It seems to make sense to make these three cases consistent
with everything else.

It took me a while to understand how DCH_keywords[] and DCH_index[]
actually work, and I think it's a pretty confusing design, but what
the patch does seems to be consistent with that, so it appears correct
to me.

Therefore, I have committed it.

--
Robert Haas
EDB: http://www.enterprisedb.com