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

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

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

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

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

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

+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

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:

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.

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

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.

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

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