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:

> +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
>