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