Re: Query about time zone patterns in to_char
> 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 wrote: > > On Fri, Jul 9, 2021 at 10:44 AM Tomas Vondra > 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
Re: Query about time zone patterns in to_char
On Fri, Jul 9, 2021 at 10:44 AM Tomas Vondra 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
Re: Query about time zone patterns in to_char
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.
Re: Query about time zone patterns in to_char
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
Re: Query about time zone patterns in to_char
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 https://momjian.us EDB https://enterprisedb.com If only the physical world exists, free will is an illusion.
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.khar...@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 < > nitinjadhavpostg...@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 >> 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 wrote: >> > >> > Nitin Jadhav 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 > v2_support_of_tzh_tzm_patterns.patch Description: Binary data
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 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 > 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 wrote: > > > > Nitin Jadhav 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
Re: Query about time zone patterns in to_char
> 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 wrote: > > On Mon, 17 May 2021 at 06:23, Nitin Jadhav > 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
Re: Query about time zone patterns in to_char
On Mon, 17 May 2021 at 06:23, Nitin Jadhav 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
Re: Query about time zone patterns in to_char
> 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 wrote: > > Nitin Jadhav 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 v1_support_of_tzh_tzm_patters.patch Description: Binary data
Re: Query about time zone patterns in to_char
Nitin Jadhav 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
Re: Query about time zone patterns in to_char
> 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 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 wrote: > > Nitin Jadhav 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
Re: Query about time zone patterns in to_char
Nitin Jadhav 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
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
