Thread: Query about time zone patterns in to_char

Hi Hackers,

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.
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, '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, 'HH');
 to_char
---------
 08
(1 row)

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

Thanks & Regards,
Nitin Jadhav

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

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

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

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

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

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