Horton

Thread

Commits

  1. Support "of", "tzh", and "tzm" format codes.

  2. Implement TZH and TZM timestamp format patterns

  1. Query about time zone patterns in to_char

    Nitin Jadhav <nitinjadhavpostgres@gmail.com> — 2021-05-16T14:55:48Z

    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

  2. Re: Query about time zone patterns in to_char

    Tom Lane <tgl@sss.pgh.pa.us> — 2021-05-16T15:10:05Z

    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

  3. Re: Query about time zone patterns in to_char

    Nitin Jadhav <nitinjadhavpostgres@gmail.com> — 2021-05-16T16:13:21Z

    > 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

  4. Re: Query about time zone patterns in to_char

    Tom Lane <tgl@sss.pgh.pa.us> — 2021-05-16T17:04:15Z

    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

  5. Re: Query about time zone patterns in to_char

    Nitin Jadhav <nitinjadhavpostgres@gmail.com> — 2021-05-16T18:22:41Z

    > 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

  6. Re: Query about time zone patterns in to_char

    David Rowley <dgrowleyml@gmail.com> — 2021-05-17T01:35:20Z

    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

  7. Re: Query about time zone patterns in to_char

    Nitin Jadhav <nitinjadhavpostgres@gmail.com> — 2021-05-17T04:22:18Z

    > 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

  8. Re: Query about time zone patterns in to_char

    Suraj Kharage <suraj.kharage@enterprisedb.com> — 2021-05-20T03:24:56Z

    +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

  9. Re: Query about time zone patterns in to_char

    Nitin Jadhav <nitinjadhavpostgres@gmail.com> — 2021-05-20T06:51:12Z

    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
>

  10. Re: Query about time zone patterns in to_char

    Bruce Momjian <bruce@momjian.us> — 2021-05-20T18:25:15Z

    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. Re: Query about time zone patterns in to_char

    Tomas Vondra <tomas.vondra@enterprisedb.com> — 2021-07-09T14:43:46Z

    
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. Re: Query about time zone patterns in to_char

    David Zhang <david.zhang@highgo.ca> — 2021-07-09T19:17:45Z

    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. Re: Query about time zone patterns in to_char

    Robert Haas <robertmhaas@gmail.com> — 2022-03-14T20:52:32Z

    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. Re: Query about time zone patterns in to_char

    Nitin Jadhav <nitinjadhavpostgres@gmail.com> — 2022-03-15T09:32:59Z

    > 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