Re: [PR] [CALCITE-6358] Support all PostgreSQL 14 date/time patterns [calcite]
mihaibudiu commented on PR #3773: URL: https://github.com/apache/calcite/pull/3773#issuecomment-2140204359 Closing in favor of #3792 -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org
Re: [PR] [CALCITE-6358] Support all PostgreSQL 14 date/time patterns [calcite]
mihaibudiu closed pull request #3773: [CALCITE-6358] Support all PostgreSQL 14 date/time patterns URL: https://github.com/apache/calcite/pull/3773 -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org
Re: [PR] [CALCITE-6358] Support all PostgreSQL 14 date/time patterns [calcite]
mihaibudiu commented on PR #3773: URL: https://github.com/apache/calcite/pull/3773#issuecomment-2113141295 Anything that works correctly is fine for me. Please note that I am on vacation so I won't have time to review this soon. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org
Re: [PR] [CALCITE-6358] Support all PostgreSQL 14 date/time patterns [calcite]
sonarcloud[bot] commented on PR #3773: URL: https://github.com/apache/calcite/pull/3773#issuecomment-2113069331 ## [](https://sonarcloud.io/dashboard?id=apache_calcite=3773) **Quality Gate passed** Issues  [10 New issues](https://sonarcloud.io/project/issues?id=apache_calcite=3773=false=true)  [0 Accepted issues](https://sonarcloud.io/component_measures?id=apache_calcite=3773=new_accepted_issues=list) Measures  [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite=3773=false=true)  [87.2% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_calcite=3773=new_coverage=list)  [6.6% Duplication on New Code](https://sonarcloud.io/component_measures?id=apache_calcite=3773=new_duplicated_lines_density=list) [See analysis details on SonarCloud](https://sonarcloud.io/dashboard?id=apache_calcite=3773) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org
Re: [PR] [CALCITE-6358] Support all PostgreSQL 14 date/time patterns [calcite]
normanj-bitquill commented on PR #3773: URL: https://github.com/apache/calcite/pull/3773#issuecomment-2113053216 @mihaibudiu I tried creating a new PR here, that did build correctly. https://github.com/apache/calcite/pull/3792 If you are OK with it, we should just use that PR and close this one. I think that there are a couple of things going on. * I had squashed commits, which could be interfering with how changes are detected in autostyle. Lesson learned, I won't squash commits. * Some Kotlin libraries are missing from the classpath for autostyle. Not an issue unless some changes are detected to a Kotlin file. The issue here may show up again if someone makes changes to `build.gradle.kts` -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org
Re: [PR] [CALCITE-6358] Support all PostgreSQL 14 date/time patterns [calcite]
sonarcloud[bot] commented on PR #3773: URL: https://github.com/apache/calcite/pull/3773#issuecomment-2111333588 ## [](https://sonarcloud.io/dashboard?id=apache_calcite=3773) **Quality Gate passed** Issues  [10 New issues](https://sonarcloud.io/project/issues?id=apache_calcite=3773=false=true)  [0 Accepted issues](https://sonarcloud.io/component_measures?id=apache_calcite=3773=new_accepted_issues=list) Measures  [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite=3773=false=true)  [87.2% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_calcite=3773=new_coverage=list)  [6.6% Duplication on New Code](https://sonarcloud.io/component_measures?id=apache_calcite=3773=new_duplicated_lines_density=list) [See analysis details on SonarCloud](https://sonarcloud.io/dashboard?id=apache_calcite=3773) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org
Re: [PR] [CALCITE-6358] Support all PostgreSQL 14 date/time patterns [calcite]
sonarcloud[bot] commented on PR #3773: URL: https://github.com/apache/calcite/pull/3773#issuecomment-2111330588 ## [](https://sonarcloud.io/dashboard?id=apache_calcite=3773) **Quality Gate passed** Issues  [10 New issues](https://sonarcloud.io/project/issues?id=apache_calcite=3773=false=true)  [0 Accepted issues](https://sonarcloud.io/component_measures?id=apache_calcite=3773=new_accepted_issues=list) Measures  [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite=3773=false=true)  [87.2% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_calcite=3773=new_coverage=list)  [6.6% Duplication on New Code](https://sonarcloud.io/component_measures?id=apache_calcite=3773=new_duplicated_lines_density=list) [See analysis details on SonarCloud](https://sonarcloud.io/dashboard?id=apache_calcite=3773) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org
Re: [PR] [CALCITE-6358] Support all PostgreSQL 14 date/time patterns [calcite]
sonarcloud[bot] commented on PR #3773: URL: https://github.com/apache/calcite/pull/3773#issuecomment-2111322200 Please retry analysis of this Pull-Request directly on SonarCloud -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org
Re: [PR] [CALCITE-6358] Support all PostgreSQL 14 date/time patterns [calcite]
sonarcloud[bot] commented on PR #3773: URL: https://github.com/apache/calcite/pull/3773#issuecomment-2111322103 ## [](https://sonarcloud.io/dashboard?id=apache_calcite=3773) **Quality Gate passed** Issues  [10 New issues](https://sonarcloud.io/project/issues?id=apache_calcite=3773=false=true)  [0 Accepted issues](https://sonarcloud.io/component_measures?id=apache_calcite=3773=new_accepted_issues=list) Measures  [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite=3773=false=true)  [87.2% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_calcite=3773=new_coverage=list)  [6.6% Duplication on New Code](https://sonarcloud.io/component_measures?id=apache_calcite=3773=new_duplicated_lines_density=list) [See analysis details on SonarCloud](https://sonarcloud.io/dashboard?id=apache_calcite=3773) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org
Re: [PR] [CALCITE-6358] Support all PostgreSQL 14 date/time patterns [calcite]
normanj-bitquill commented on PR #3773: URL: https://github.com/apache/calcite/pull/3773#issuecomment-2111284526 Locally with a fresh checkout I get: ``` Skipping task ':buildSrc:autostyleKotlinGradleCheck' as it is up-to-date. ``` For the Windows build, I get: ``` :buildSrc:autostyleKotlinGradleCheck (Thread[Execution worker,5,main]) started. > Task :buildSrc:autostyleKotlinGradleCheck FAILED Caching disabled for task ':buildSrc:autostyleKotlinGradleCheck' because: Caching has not been enabled for the task Task ':buildSrc:autostyleKotlinGradleCheck' is not up-to-date because: No history is available. The input changes require a full rebuild for incremental task ':buildSrc:autostyleKotlinGradleCheck'. ``` -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org
Re: [PR] [CALCITE-6358] Support all PostgreSQL 14 date/time patterns [calcite]
normanj-bitquill commented on PR #3773: URL: https://github.com/apache/calcite/pull/3773#issuecomment-2111278392 I have no idea about the Windows build errors. It fails trying to do task `:buildSrc:autostyleKotlinGradleCheck`. It fails since it can't find the class `org.jetbrains.kotlin.com.intellij.psi.tree.IElementType`. I don't see why that is missing due to the changes for this PR. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org
Re: [PR] [CALCITE-6358] Support all PostgreSQL 14 date/time patterns [calcite]
sonarcloud[bot] commented on PR #3773: URL: https://github.com/apache/calcite/pull/3773#issuecomment-2111095551 ## [](https://sonarcloud.io/dashboard?id=apache_calcite=3773) **Quality Gate passed** Issues  [10 New issues](https://sonarcloud.io/project/issues?id=apache_calcite=3773=false=true)  [0 Accepted issues](https://sonarcloud.io/component_measures?id=apache_calcite=3773=new_accepted_issues=list) Measures  [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite=3773=false=true)  [87.2% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_calcite=3773=new_coverage=list)  [6.6% Duplication on New Code](https://sonarcloud.io/component_measures?id=apache_calcite=3773=new_duplicated_lines_density=list) [See analysis details on SonarCloud](https://sonarcloud.io/dashboard?id=apache_calcite=3773) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org
Re: [PR] [CALCITE-6358] Support all PostgreSQL 14 date/time patterns [calcite]
sonarcloud[bot] commented on PR #3773: URL: https://github.com/apache/calcite/pull/3773#issuecomment-2110803385 ## [](https://sonarcloud.io/dashboard?id=apache_calcite=3773) **Quality Gate passed** Issues  [10 New issues](https://sonarcloud.io/project/issues?id=apache_calcite=3773=false=true)  [0 Accepted issues](https://sonarcloud.io/component_measures?id=apache_calcite=3773=new_accepted_issues=list) Measures  [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite=3773=false=true)  [87.2% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_calcite=3773=new_coverage=list)  [6.6% Duplication on New Code](https://sonarcloud.io/component_measures?id=apache_calcite=3773=new_duplicated_lines_density=list) [See analysis details on SonarCloud](https://sonarcloud.io/dashboard?id=apache_calcite=3773) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org
Re: [PR] [CALCITE-6358] Support all PostgreSQL 14 date/time patterns [calcite]
normanj-bitquill commented on PR #3773: URL: https://github.com/apache/calcite/pull/3773#issuecomment-2110538944 > I don't understand why the CI fails. Can you please rebase on main? Maybe it has something to do with the recent transition to 1.37 which had some build system changes. @mihaibudiu I have rebased to the latest for main. CI still fails for "Windows (JDK 8)" and "Windows (JDK 17)". -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org
Re: [PR] [CALCITE-6358] Support all PostgreSQL 14 date/time patterns [calcite]
mihaibudiu commented on PR #3773: URL: https://github.com/apache/calcite/pull/3773#issuecomment-2109039604 I don't understand why the CI fails. Can you please rebase on main? Maybe it has something to do with the recent transition to 1.37 which had some build system changes. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org
Re: [PR] [CALCITE-6358] Support all PostgreSQL 14 date/time patterns [calcite]
sonarcloud[bot] commented on PR #3773: URL: https://github.com/apache/calcite/pull/3773#issuecomment-2108837190 ## [](https://sonarcloud.io/dashboard?id=apache_calcite=3773) **Quality Gate passed** Issues  [14 New issues](https://sonarcloud.io/project/issues?id=apache_calcite=3773=false=true)  [0 Accepted issues](https://sonarcloud.io/component_measures?id=apache_calcite=3773=new_accepted_issues=list) Measures  [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite=3773=false=true)  [87.2% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_calcite=3773=new_coverage=list)  [6.6% Duplication on New Code](https://sonarcloud.io/component_measures?id=apache_calcite=3773=new_duplicated_lines_density=list) [See analysis details on SonarCloud](https://sonarcloud.io/dashboard?id=apache_calcite=3773) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org
Re: [PR] [CALCITE-6358] Support all PostgreSQL 14 date/time patterns [calcite]
normanj-bitquill commented on code in PR #3773:
URL: https://github.com/apache/calcite/pull/3773#discussion_r1598814257
##
core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java:
##
@@ -1651,13 +1652,22 @@ private static RelDataType
deriveTypeMapFromEntries(SqlOperatorBinding opBinding
*
* ({@code TO_CHAR} is not supported in MySQL, but it is supported in
* MariaDB, a variant of MySQL covered by {@link SqlLibrary#MYSQL}.) */
- @LibraryOperator(libraries = {MYSQL, ORACLE, POSTGRESQL})
+ @LibraryOperator(libraries = {MYSQL, ORACLE})
public static final SqlFunction TO_CHAR =
SqlBasicFunction.create("TO_CHAR",
ReturnTypes.VARCHAR,
OperandTypes.TIMESTAMP_STRING,
SqlFunctionCategory.TIMEDATE);
+ /** The "TO_CHAR(timestamp, format)" function;
+ * converts {@code timestamp} to string according to the given {@code
format}. */
+ @LibraryOperator(libraries = {POSTGRESQL})
+ public static final SqlFunction TO_CHAR_PG =
+ new SqlBasicFunction("TO_CHAR", SqlKind.OTHER_FUNCTION,
+ SqlSyntax.FUNCTION, true, ReturnTypes.VARCHAR, null,
Review Comment:
Good point, this has been updated.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
Re: [PR] [CALCITE-6358] Support all PostgreSQL 14 date/time patterns [calcite]
arkanovicz commented on code in PR #3773:
URL: https://github.com/apache/calcite/pull/3773#discussion_r1597705152
##
core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java:
##
@@ -1651,13 +1652,22 @@ private static RelDataType
deriveTypeMapFromEntries(SqlOperatorBinding opBinding
*
* ({@code TO_CHAR} is not supported in MySQL, but it is supported in
* MariaDB, a variant of MySQL covered by {@link SqlLibrary#MYSQL}.) */
- @LibraryOperator(libraries = {MYSQL, ORACLE, POSTGRESQL})
+ @LibraryOperator(libraries = {MYSQL, ORACLE})
public static final SqlFunction TO_CHAR =
SqlBasicFunction.create("TO_CHAR",
ReturnTypes.VARCHAR,
OperandTypes.TIMESTAMP_STRING,
SqlFunctionCategory.TIMEDATE);
+ /** The "TO_CHAR(timestamp, format)" function;
+ * converts {@code timestamp} to string according to the given {@code
format}. */
+ @LibraryOperator(libraries = {POSTGRESQL})
+ public static final SqlFunction TO_CHAR_PG =
+ new SqlBasicFunction("TO_CHAR", SqlKind.OTHER_FUNCTION,
+ SqlSyntax.FUNCTION, true, ReturnTypes.VARCHAR, null,
Review Comment:
Why not ReturnTypes.VARCHAR_NULLABLE here? The original Calcite TO_CHAR
function result should also be nullable, by the way.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
Re: [PR] [CALCITE-6358] Support all PostgreSQL 14 date/time patterns [calcite]
sonarcloud[bot] commented on PR #3773: URL: https://github.com/apache/calcite/pull/3773#issuecomment-2104930854 ## [](https://sonarcloud.io/dashboard?id=apache_calcite=3773) **Quality Gate passed** Issues  [14 New issues](https://sonarcloud.io/project/issues?id=apache_calcite=3773=false=true)  [0 Accepted issues](https://sonarcloud.io/component_measures?id=apache_calcite=3773=new_accepted_issues=list) Measures  [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite=3773=false=true)  [87.2% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_calcite=3773=new_coverage=list)  [6.6% Duplication on New Code](https://sonarcloud.io/component_measures?id=apache_calcite=3773=new_duplicated_lines_density=list) [See analysis details on SonarCloud](https://sonarcloud.io/dashboard?id=apache_calcite=3773) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org
Re: [PR] [CALCITE-6358] Support all PostgreSQL 14 date/time patterns [calcite]
sonarcloud[bot] commented on PR #3773: URL: https://github.com/apache/calcite/pull/3773#issuecomment-2104906834 ## [](https://sonarcloud.io/dashboard?id=apache_calcite=3773) **Quality Gate passed** Issues  [14 New issues](https://sonarcloud.io/project/issues?id=apache_calcite=3773=false=true)  [0 Accepted issues](https://sonarcloud.io/component_measures?id=apache_calcite=3773=new_accepted_issues=list) Measures  [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite=3773=false=true)  [87.2% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_calcite=3773=new_coverage=list)  [6.6% Duplication on New Code](https://sonarcloud.io/component_measures?id=apache_calcite=3773=new_duplicated_lines_density=list) [See analysis details on SonarCloud](https://sonarcloud.io/dashboard?id=apache_calcite=3773) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org
Re: [PR] [CALCITE-6358] Support all PostgreSQL 14 date/time patterns [calcite]
sonarcloud[bot] commented on PR #3773: URL: https://github.com/apache/calcite/pull/3773#issuecomment-2088799632 ## [](https://sonarcloud.io/dashboard?id=apache_calcite=3773) **Quality Gate passed** Issues  [12 New issues](https://sonarcloud.io/project/issues?id=apache_calcite=3773=false=true)  [0 Accepted issues](https://sonarcloud.io/component_measures?id=apache_calcite=3773=new_accepted_issues=list) Measures  [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite=3773=false=true)  [87.2% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_calcite=3773=new_coverage=list)  [6.6% Duplication on New Code](https://sonarcloud.io/component_measures?id=apache_calcite=3773=new_duplicated_lines_density=list) [See analysis details on SonarCloud](https://sonarcloud.io/dashboard?id=apache_calcite=3773) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org
Re: [PR] [CALCITE-6358] Support all PostgreSQL 14 date/time patterns [calcite]
mihaibudiu commented on PR #3773: URL: https://github.com/apache/calcite/pull/3773#issuecomment-2088787954 This looks very good, when they unfreeze the main branch we can merge this. Regarding the script, it's good for this particular use case, but I am not sure whether it will generalize for arbitrary queries. But we can take this discussion to the other JIRA ticket (the preferred way to design solutions in Calcite is to discuss in JIRA). Some Calcite-isms leak into the iq file, like $EXPR0, so a true solution would probably have a different architecture. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org
Re: [PR] [CALCITE-6358] Support all PostgreSQL 14 date/time patterns [calcite]
normanj-bitquill commented on PR #3773: URL: https://github.com/apache/calcite/pull/3773#issuecomment-2088777985 @mihaibudiu > This is pretty close, the existing file `core/src/test/resources/sql/sub-query.iq` already uses the postgres format. What I would do is to make a small python script to convert the postgres output into a .iq file, which you can hopefully just add to the resources directory in java. Then, before you commit, make sure the tests in the file are being executed by introducing a typo. And please add a comment to the iq file stating the Postgres version that was used to generate it. I am not sure whether there is some place where the python script itself could be committed... Added the queries here: https://github.com/Bit-Quill/calcite/blob/calcite-6358/core/src/test/resources/pg_to_char_queries.sql Here is a script that is able to generate the *.iq file: https://github.com/Bit-Quill/calcite/blob/calcite-6358/core/src/test/resources/to_char_generate_iq.py Ran the `:core:test` target with the generated *.iq file. Verified that the file is run by adding an error. The generated file is executed successfully with no errors. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org
Re: [PR] [CALCITE-6358] Support all PostgreSQL 14 date/time patterns [calcite]
mihaibudiu commented on PR #3773: URL: https://github.com/apache/calcite/pull/3773#issuecomment-2086209425 This is pretty close, the existing file `core/src/test/resources/sql/sub-query.iq` already uses the postgres format. What I would do is to make a small python script to convert the postgres output into a .iq file, which you can hopefully just add to the resources directory in java. Then, before you commit, make sure the tests in the file are being executed by introducing a typo. And please add a comment to the iq file stating the Postgres version that was used to generate it. I am not sure whether there is some place where the python script itself could be committed... -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org
Re: [PR] [CALCITE-6358] Support all PostgreSQL 14 date/time patterns [calcite]
normanj-bitquill commented on PR #3773: URL: https://github.com/apache/calcite/pull/3773#issuecomment-2085910494 @mihaibudiu Here is the output of a PostgreSQL script running all of the `TO_CHAR` tests from the `postgresql.iq` file in this PR. I kept the expected output for Calcite in SQL comments to make comparison easier. This is the SQL script. [to_char.txt](https://github.com/apache/calcite/files/15168101/to_char.txt) This is the output when running it on PostgreSQL 14. [to_char_results.txt](https://github.com/apache/calcite/files/15168106/to_char_results.txt) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org
Re: [PR] [CALCITE-6358] Support all PostgreSQL 14 date/time patterns [calcite]
normanj-bitquill commented on PR #3773: URL: https://github.com/apache/calcite/pull/3773#issuecomment-2083809944 @mihaibudiu > Until https://issues.apache.org/jira/browse/CALCITE-6281 has a solution, the best way to test this may be to create a Quidem test file (.iq suffix). If that file is identical with the Postgres shell output, you're done. (I have no idea whether the quidem output is compatible with Postgres, though). Thanks, I'll take a look. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org
Re: [PR] [CALCITE-6358] Support all PostgreSQL 14 date/time patterns [calcite]
normanj-bitquill commented on code in PR #3773:
URL: https://github.com/apache/calcite/pull/3773#discussion_r1583854856
##
core/src/test/java/org/apache/calcite/util/format/PostgresqlDateTimeFormatterTest.java:
##
@@ -0,0 +1,1238 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to you under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.calcite.util.format;
+
+import org.junit.jupiter.api.Test;
+import org.junit.jupiter.params.ParameterizedTest;
+import org.junit.jupiter.params.provider.ValueSource;
+
+import java.time.LocalDateTime;
+import java.time.ZoneId;
+import java.time.ZonedDateTime;
+import java.util.Locale;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+
+/**
+ * Unit test for {@link PostgresqlDateTimeFormatter}.
+ */
+public class PostgresqlDateTimeFormatterTest {
+ @ParameterizedTest
+ @ValueSource(strings = {"HH12", "HH"})
+ void testHH12(String pattern) {
+final ZonedDateTime midnight = createDateTime(2024, 1, 1, 0, 0, 0, 0);
+final ZonedDateTime morning = createDateTime(2024, 1, 1, 6, 0, 0, 0);
+final ZonedDateTime noon = createDateTime(2024, 1, 1, 12, 0, 0, 0);
+final ZonedDateTime evening = createDateTime(2024, 1, 1, 18, 0, 0, 0);
+
+assertEquals("12", PostgresqlDateTimeFormatter.toChar(pattern, midnight));
+assertEquals("06", PostgresqlDateTimeFormatter.toChar(pattern, morning));
+assertEquals("12", PostgresqlDateTimeFormatter.toChar(pattern, noon));
+assertEquals("06", PostgresqlDateTimeFormatter.toChar(pattern, evening));
+assertEquals(
+"12", PostgresqlDateTimeFormatter.toChar("FM" + pattern,
+midnight));
+assertEquals(
+"6", PostgresqlDateTimeFormatter.toChar("FM" + pattern,
+morning));
+assertEquals(
+"12", PostgresqlDateTimeFormatter.toChar("FM" + pattern,
+noon));
+assertEquals(
+"6", PostgresqlDateTimeFormatter.toChar("FM" + pattern,
+evening));
+
+final ZonedDateTime hourOne = createDateTime(2024, 1, 1, 1, 0, 0, 0);
+final ZonedDateTime hourTwo = createDateTime(2024, 1, 1, 2, 0, 0, 0);
+final ZonedDateTime hourThree = createDateTime(2024, 1, 1, 3, 0, 0, 0);
+assertEquals(
+"12TH", PostgresqlDateTimeFormatter.toChar(pattern + "TH",
+midnight));
+assertEquals(
+"01ST", PostgresqlDateTimeFormatter.toChar(pattern + "TH",
+hourOne));
+assertEquals(
+"02ND", PostgresqlDateTimeFormatter.toChar(pattern + "TH",
+hourTwo));
+assertEquals(
+"03RD", PostgresqlDateTimeFormatter.toChar(pattern + "TH",
+hourThree));
+assertEquals(
+"12th", PostgresqlDateTimeFormatter.toChar(pattern + "th",
+midnight));
+assertEquals(
+"01st", PostgresqlDateTimeFormatter.toChar(pattern + "th",
+hourOne));
+assertEquals(
+"02nd", PostgresqlDateTimeFormatter.toChar(pattern + "th",
+hourTwo));
+assertEquals(
+"03rd", PostgresqlDateTimeFormatter.toChar(pattern + "th",
+hourThree));
+
+assertEquals(
+"2nd", PostgresqlDateTimeFormatter.toChar(
+"FM" + pattern + "th", hourTwo));
+ }
+
+ @Test void testHH24() {
+final ZonedDateTime midnight = createDateTime(2024, 1, 1, 0, 0, 0, 0);
+final ZonedDateTime morning = createDateTime(2024, 1, 1, 6, 0, 0, 0);
+final ZonedDateTime noon = createDateTime(2024, 1, 1, 12, 0, 0, 0);
+final ZonedDateTime evening = createDateTime(2024, 1, 1, 18, 0, 0, 0);
+
+assertEquals("00", PostgresqlDateTimeFormatter.toChar("HH24", midnight));
+assertEquals("06", PostgresqlDateTimeFormatter.toChar("HH24", morning));
+assertEquals("12", PostgresqlDateTimeFormatter.toChar("HH24", noon));
+assertEquals("18", PostgresqlDateTimeFormatter.toChar("HH24", evening));
+assertEquals("0", PostgresqlDateTimeFormatter.toChar("FMHH24", midnight));
+assertEquals("6", PostgresqlDateTimeFormatter.toChar("FMHH24", morning));
+assertEquals("12", PostgresqlDateTimeFormatter.toChar("FMHH24", noon));
+assertEquals("18", PostgresqlDateTimeFormatter.toChar("FMHH24", evening));
+
+final ZonedDateTime hourOne = createDateTime(2024, 1, 1, 1, 0, 0, 0);
+final ZonedDateTime hourTwo = createDateTime(2024, 1, 1, 2, 0,
Re: [PR] [CALCITE-6358] Support all PostgreSQL 14 date/time patterns [calcite]
normanj-bitquill commented on code in PR #3773:
URL: https://github.com/apache/calcite/pull/3773#discussion_r1583854741
##
core/src/main/java/org/apache/calcite/util/format/PostgresqlDateTimeFormatter.java:
##
@@ -0,0 +1,660 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to you under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.calcite.util.format;
+
+import java.text.ParsePosition;
+import java.time.Month;
+import java.time.ZonedDateTime;
+import java.time.format.TextStyle;
+import java.time.temporal.ChronoField;
+import java.time.temporal.IsoFields;
+import java.time.temporal.JulianFields;
+import java.util.Locale;
+import java.util.function.Function;
+
+/**
+ * Provides an implementation of toChar that matches PostgreSQL behaviour.
+ */
+public class PostgresqlDateTimeFormatter {
+ /**
+ * Result of applying a format element to the current position in the format
+ * string. If matched, will contain the output from applying the format
+ * element.
+ */
+ private static class PatternConvertResult {
+final boolean matched;
+final String formattedString;
+
+protected PatternConvertResult() {
+ matched = false;
+ formattedString = "";
+}
+
+protected PatternConvertResult(boolean matched, String formattedString) {
+ this.matched = matched;
+ this.formattedString = formattedString;
+}
+ }
+
+ /**
+ * A format element that is able to produce a string from a date.
+ */
+ private interface FormatPattern {
+/**
+ * Checks if the current position in the format string applies to this
format
+ * element. Will produce a formatted string if matched that is the format
element
Review Comment:
I've tried to reword this.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
Re: [PR] [CALCITE-6358] Support all PostgreSQL 14 date/time patterns [calcite]
normanj-bitquill commented on code in PR #3773:
URL: https://github.com/apache/calcite/pull/3773#discussion_r1583854483
##
core/src/main/java/org/apache/calcite/util/format/PostgresqlDateTimeFormatter.java:
##
@@ -0,0 +1,660 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to you under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.calcite.util.format;
+
+import java.text.ParsePosition;
+import java.time.Month;
+import java.time.ZonedDateTime;
+import java.time.format.TextStyle;
+import java.time.temporal.ChronoField;
+import java.time.temporal.IsoFields;
+import java.time.temporal.JulianFields;
+import java.util.Locale;
+import java.util.function.Function;
+
+/**
+ * Provides an implementation of toChar that matches PostgreSQL behaviour.
+ */
+public class PostgresqlDateTimeFormatter {
+ /**
+ * Result of applying a format element to the current position in the format
+ * string. If matched, will contain the output from applying the format
+ * element.
+ */
+ private static class PatternConvertResult {
+final boolean matched;
+final String formattedString;
+
+protected PatternConvertResult() {
+ matched = false;
+ formattedString = "";
+}
+
+protected PatternConvertResult(boolean matched, String formattedString) {
+ this.matched = matched;
+ this.formattedString = formattedString;
+}
+ }
+
+ /**
+ * A format element that is able to produce a string from a date.
+ */
+ private interface FormatPattern {
+/**
+ * Checks if the current position in the format string applies to this
format
+ * element. Will produce a formatted string if matched that is the format
element
+ * applied to the datetime. If matched will also update the parser
position.
+ *
+ * @param parsePosition current position in the format string
+ * @param formatString input format string
+ * @param dateTime datetime to convert
+ * @return the string representation of the datetime based on the format
pattern
+ */
+PatternConvertResult convert(ParsePosition parsePosition, String
formatString,
+ZonedDateTime dateTime);
+ }
+
+ /**
+ * A format element that will produce a number. Nubmers can have leading
zeroes
+ * removed and can have ordinal suffixes.
+ */
+ private static class NumberFormatPattern implements FormatPattern {
+private final String[] patterns;
+private final Function converter;
+
+protected NumberFormatPattern(Function converter,
String... patterns) {
+ this.converter = converter;
+ this.patterns = patterns;
+}
+
+@Override public PatternConvertResult convert(ParsePosition parsePosition,
String formatString,
+ZonedDateTime dateTime) {
+ final String formatStringTrimmed =
formatString.substring(parsePosition.getIndex());
+
+ String patternToUse = null;
+ for (String pattern : patterns) {
+if (formatStringTrimmed.startsWith(pattern)
+|| formatStringTrimmed.startsWith("FM" + pattern)) {
+ patternToUse = pattern;
+ break;
+}
+ }
+
+ if (patternToUse == null) {
+return NO_PATTERN_MATCH;
+ }
+
+ parsePosition.setIndex(parsePosition.getIndex() + patternToUse.length());
+
+ boolean fillMode = !formatStringTrimmed.startsWith("FM");
+ String ordinalSuffix = null;
+ if (formatStringTrimmed.startsWith("FM" + patternToUse + "TH")) {
+fillMode = false;
+ordinalSuffix = "TH";
+ } else if (formatStringTrimmed.startsWith("FM" + patternToUse + "th")) {
+fillMode = false;
+ordinalSuffix = "th";
+ } else if (formatStringTrimmed.startsWith(patternToUse + "TH")) {
+ordinalSuffix = "TH";
+ } else if (formatStringTrimmed.startsWith(patternToUse + "th")) {
+ordinalSuffix = "th";
+ }
+
+ String formattedValue = converter.apply(dateTime);
+ if (!fillMode) {
+formattedValue = trimLeadingZeros(formattedValue);
+parsePosition.setIndex(parsePosition.getIndex() + 2);
+ }
+
+ if (ordinalSuffix != null) {
+String suffix;
+
+if (formattedValue.length() >= 2
+&& formattedValue.charAt(formattedValue.length() - 2) == '1') {
+
Re: [PR] [CALCITE-6358] Support all PostgreSQL 14 date/time patterns [calcite]
normanj-bitquill commented on PR #3773: URL: https://github.com/apache/calcite/pull/3773#issuecomment-2083797456 @caicancai > I remember that calcite currently has support for the postgres to_char function. Should we remove the previous support? The existing TO_CHAR function is used by MySQL and Oracle, so it is left in here. This PR adds a new implementation that only PostgreSQL uses. https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java#L1654 -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org
Re: [PR] [CALCITE-6358] Support all PostgreSQL 14 date/time patterns [calcite]
sonarcloud[bot] commented on PR #3773: URL: https://github.com/apache/calcite/pull/3773#issuecomment-2083758777 ## [](https://sonarcloud.io/dashboard?id=apache_calcite=3773) **Quality Gate passed** Issues  [12 New issues](https://sonarcloud.io/project/issues?id=apache_calcite=3773=false=true)  [0 Accepted issues](https://sonarcloud.io/component_measures?id=apache_calcite=3773=new_accepted_issues=list) Measures  [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite=3773=false=true)  [88.0% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_calcite=3773=new_coverage=list)  [6.6% Duplication on New Code](https://sonarcloud.io/component_measures?id=apache_calcite=3773=new_duplicated_lines_density=list) [See analysis details on SonarCloud](https://sonarcloud.io/dashboard?id=apache_calcite=3773) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org
Re: [PR] [CALCITE-6358] Support all PostgreSQL 14 date/time patterns [calcite]
normanj-bitquill commented on code in PR #3773:
URL: https://github.com/apache/calcite/pull/3773#discussion_r1583472991
##
core/src/test/java/org/apache/calcite/util/format/PostgresqlDateTimeFormatterTest.java:
##
@@ -0,0 +1,1238 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to you under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.calcite.util.format;
+
+import org.junit.jupiter.api.Test;
+import org.junit.jupiter.params.ParameterizedTest;
+import org.junit.jupiter.params.provider.ValueSource;
+
+import java.time.LocalDateTime;
+import java.time.ZoneId;
+import java.time.ZonedDateTime;
+import java.util.Locale;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+
+/**
+ * Unit test for {@link PostgresqlDateTimeFormatter}.
+ */
+public class PostgresqlDateTimeFormatterTest {
+ @ParameterizedTest
+ @ValueSource(strings = {"HH12", "HH"})
+ void testHH12(String pattern) {
+final ZonedDateTime midnight = createDateTime(2024, 1, 1, 0, 0, 0, 0);
+final ZonedDateTime morning = createDateTime(2024, 1, 1, 6, 0, 0, 0);
+final ZonedDateTime noon = createDateTime(2024, 1, 1, 12, 0, 0, 0);
+final ZonedDateTime evening = createDateTime(2024, 1, 1, 18, 0, 0, 0);
+
+assertEquals("12", PostgresqlDateTimeFormatter.toChar(pattern, midnight));
+assertEquals("06", PostgresqlDateTimeFormatter.toChar(pattern, morning));
+assertEquals("12", PostgresqlDateTimeFormatter.toChar(pattern, noon));
+assertEquals("06", PostgresqlDateTimeFormatter.toChar(pattern, evening));
+assertEquals(
+"12", PostgresqlDateTimeFormatter.toChar("FM" + pattern,
+midnight));
+assertEquals(
+"6", PostgresqlDateTimeFormatter.toChar("FM" + pattern,
+morning));
+assertEquals(
+"12", PostgresqlDateTimeFormatter.toChar("FM" + pattern,
+noon));
+assertEquals(
+"6", PostgresqlDateTimeFormatter.toChar("FM" + pattern,
+evening));
+
+final ZonedDateTime hourOne = createDateTime(2024, 1, 1, 1, 0, 0, 0);
+final ZonedDateTime hourTwo = createDateTime(2024, 1, 1, 2, 0, 0, 0);
+final ZonedDateTime hourThree = createDateTime(2024, 1, 1, 3, 0, 0, 0);
+assertEquals(
+"12TH", PostgresqlDateTimeFormatter.toChar(pattern + "TH",
+midnight));
+assertEquals(
+"01ST", PostgresqlDateTimeFormatter.toChar(pattern + "TH",
+hourOne));
+assertEquals(
+"02ND", PostgresqlDateTimeFormatter.toChar(pattern + "TH",
+hourTwo));
+assertEquals(
+"03RD", PostgresqlDateTimeFormatter.toChar(pattern + "TH",
+hourThree));
+assertEquals(
+"12th", PostgresqlDateTimeFormatter.toChar(pattern + "th",
+midnight));
+assertEquals(
+"01st", PostgresqlDateTimeFormatter.toChar(pattern + "th",
+hourOne));
+assertEquals(
+"02nd", PostgresqlDateTimeFormatter.toChar(pattern + "th",
+hourTwo));
+assertEquals(
+"03rd", PostgresqlDateTimeFormatter.toChar(pattern + "th",
+hourThree));
+
+assertEquals(
+"2nd", PostgresqlDateTimeFormatter.toChar(
+"FM" + pattern + "th", hourTwo));
+ }
+
+ @Test void testHH24() {
+final ZonedDateTime midnight = createDateTime(2024, 1, 1, 0, 0, 0, 0);
+final ZonedDateTime morning = createDateTime(2024, 1, 1, 6, 0, 0, 0);
+final ZonedDateTime noon = createDateTime(2024, 1, 1, 12, 0, 0, 0);
+final ZonedDateTime evening = createDateTime(2024, 1, 1, 18, 0, 0, 0);
+
+assertEquals("00", PostgresqlDateTimeFormatter.toChar("HH24", midnight));
+assertEquals("06", PostgresqlDateTimeFormatter.toChar("HH24", morning));
+assertEquals("12", PostgresqlDateTimeFormatter.toChar("HH24", noon));
+assertEquals("18", PostgresqlDateTimeFormatter.toChar("HH24", evening));
+assertEquals("0", PostgresqlDateTimeFormatter.toChar("FMHH24", midnight));
+assertEquals("6", PostgresqlDateTimeFormatter.toChar("FMHH24", morning));
+assertEquals("12", PostgresqlDateTimeFormatter.toChar("FMHH24", noon));
+assertEquals("18", PostgresqlDateTimeFormatter.toChar("FMHH24", evening));
+
+final ZonedDateTime hourOne = createDateTime(2024, 1, 1, 1, 0, 0, 0);
+final ZonedDateTime hourTwo = createDateTime(2024, 1, 1, 2, 0,
Re: [PR] [CALCITE-6358] Support all PostgreSQL 14 date/time patterns [calcite]
normanj-bitquill commented on code in PR #3773:
URL: https://github.com/apache/calcite/pull/3773#discussion_r1583396449
##
core/src/main/java/org/apache/calcite/util/format/PostgresqlDateTimeFormatter.java:
##
@@ -0,0 +1,660 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to you under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.calcite.util.format;
+
+import java.text.ParsePosition;
+import java.time.Month;
+import java.time.ZonedDateTime;
+import java.time.format.TextStyle;
+import java.time.temporal.ChronoField;
+import java.time.temporal.IsoFields;
+import java.time.temporal.JulianFields;
+import java.util.Locale;
+import java.util.function.Function;
+
+/**
+ * Provides an implementation of toChar that matches PostgreSQL behaviour.
+ */
+public class PostgresqlDateTimeFormatter {
+ /**
+ * Result of applying a format element to the current position in the format
+ * string. If matched, will contain the output from applying the format
+ * element.
+ */
+ private static class PatternConvertResult {
+final boolean matched;
+final String formattedString;
+
+protected PatternConvertResult() {
+ matched = false;
+ formattedString = "";
+}
+
+protected PatternConvertResult(boolean matched, String formattedString) {
+ this.matched = matched;
+ this.formattedString = formattedString;
+}
+ }
+
+ /**
+ * A format element that is able to produce a string from a date.
+ */
+ private interface FormatPattern {
+/**
+ * Checks if the current position in the format string applies to this
format
+ * element. Will produce a formatted string if matched that is the format
element
+ * applied to the datetime. If matched will also update the parser
position.
+ *
+ * @param parsePosition current position in the format string
+ * @param formatString input format string
+ * @param dateTime datetime to convert
+ * @return the string representation of the datetime based on the format
pattern
+ */
+PatternConvertResult convert(ParsePosition parsePosition, String
formatString,
+ZonedDateTime dateTime);
+ }
+
+ /**
+ * A format element that will produce a number. Nubmers can have leading
zeroes
+ * removed and can have ordinal suffixes.
+ */
+ private static class NumberFormatPattern implements FormatPattern {
+private final String[] patterns;
+private final Function converter;
+
+protected NumberFormatPattern(Function converter,
String... patterns) {
+ this.converter = converter;
+ this.patterns = patterns;
+}
+
+@Override public PatternConvertResult convert(ParsePosition parsePosition,
String formatString,
+ZonedDateTime dateTime) {
+ final String formatStringTrimmed =
formatString.substring(parsePosition.getIndex());
+
+ String patternToUse = null;
+ for (String pattern : patterns) {
+if (formatStringTrimmed.startsWith(pattern)
+|| formatStringTrimmed.startsWith("FM" + pattern)) {
+ patternToUse = pattern;
+ break;
+}
+ }
+
+ if (patternToUse == null) {
+return NO_PATTERN_MATCH;
+ }
+
+ parsePosition.setIndex(parsePosition.getIndex() + patternToUse.length());
+
+ boolean fillMode = !formatStringTrimmed.startsWith("FM");
+ String ordinalSuffix = null;
+ if (formatStringTrimmed.startsWith("FM" + patternToUse + "TH")) {
+fillMode = false;
+ordinalSuffix = "TH";
+ } else if (formatStringTrimmed.startsWith("FM" + patternToUse + "th")) {
+fillMode = false;
+ordinalSuffix = "th";
+ } else if (formatStringTrimmed.startsWith(patternToUse + "TH")) {
+ordinalSuffix = "TH";
+ } else if (formatStringTrimmed.startsWith(patternToUse + "th")) {
+ordinalSuffix = "th";
+ }
+
+ String formattedValue = converter.apply(dateTime);
+ if (!fillMode) {
+formattedValue = trimLeadingZeros(formattedValue);
+parsePosition.setIndex(parsePosition.getIndex() + 2);
+ }
+
+ if (ordinalSuffix != null) {
+String suffix;
+
+if (formattedValue.length() >= 2
+&& formattedValue.charAt(formattedValue.length() - 2) == '1') {
+
Re: [PR] [CALCITE-6358] Support all PostgreSQL 14 date/time patterns [calcite]
normanj-bitquill commented on code in PR #3773:
URL: https://github.com/apache/calcite/pull/3773#discussion_r1583396047
##
core/src/main/java/org/apache/calcite/util/format/PostgresqlDateTimeFormatter.java:
##
@@ -0,0 +1,660 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to you under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.calcite.util.format;
+
+import java.text.ParsePosition;
+import java.time.Month;
+import java.time.ZonedDateTime;
+import java.time.format.TextStyle;
+import java.time.temporal.ChronoField;
+import java.time.temporal.IsoFields;
+import java.time.temporal.JulianFields;
+import java.util.Locale;
+import java.util.function.Function;
+
+/**
+ * Provides an implementation of toChar that matches PostgreSQL behaviour.
+ */
+public class PostgresqlDateTimeFormatter {
+ /**
+ * Result of applying a format element to the current position in the format
+ * string. If matched, will contain the output from applying the format
+ * element.
+ */
+ private static class PatternConvertResult {
+final boolean matched;
+final String formattedString;
+
+protected PatternConvertResult() {
+ matched = false;
+ formattedString = "";
+}
+
+protected PatternConvertResult(boolean matched, String formattedString) {
+ this.matched = matched;
+ this.formattedString = formattedString;
+}
+ }
+
+ /**
+ * A format element that is able to produce a string from a date.
+ */
+ private interface FormatPattern {
+/**
+ * Checks if the current position in the format string applies to this
format
+ * element. Will produce a formatted string if matched that is the format
element
+ * applied to the datetime. If matched will also update the parser
position.
+ *
+ * @param parsePosition current position in the format string
+ * @param formatString input format string
+ * @param dateTime datetime to convert
+ * @return the string representation of the datetime based on the format
pattern
+ */
+PatternConvertResult convert(ParsePosition parsePosition, String
formatString,
+ZonedDateTime dateTime);
+ }
+
+ /**
+ * A format element that will produce a number. Nubmers can have leading
zeroes
+ * removed and can have ordinal suffixes.
+ */
+ private static class NumberFormatPattern implements FormatPattern {
+private final String[] patterns;
+private final Function converter;
+
+protected NumberFormatPattern(Function converter,
String... patterns) {
+ this.converter = converter;
+ this.patterns = patterns;
+}
+
+@Override public PatternConvertResult convert(ParsePosition parsePosition,
String formatString,
+ZonedDateTime dateTime) {
+ final String formatStringTrimmed =
formatString.substring(parsePosition.getIndex());
+
+ String patternToUse = null;
+ for (String pattern : patterns) {
+if (formatStringTrimmed.startsWith(pattern)
+|| formatStringTrimmed.startsWith("FM" + pattern)) {
+ patternToUse = pattern;
+ break;
+}
+ }
+
+ if (patternToUse == null) {
+return NO_PATTERN_MATCH;
+ }
+
+ parsePosition.setIndex(parsePosition.getIndex() + patternToUse.length());
+
+ boolean fillMode = !formatStringTrimmed.startsWith("FM");
+ String ordinalSuffix = null;
+ if (formatStringTrimmed.startsWith("FM" + patternToUse + "TH")) {
+fillMode = false;
+ordinalSuffix = "TH";
+ } else if (formatStringTrimmed.startsWith("FM" + patternToUse + "th")) {
+fillMode = false;
+ordinalSuffix = "th";
+ } else if (formatStringTrimmed.startsWith(patternToUse + "TH")) {
+ordinalSuffix = "TH";
+ } else if (formatStringTrimmed.startsWith(patternToUse + "th")) {
+ordinalSuffix = "th";
+ }
+
+ String formattedValue = converter.apply(dateTime);
+ if (!fillMode) {
+formattedValue = trimLeadingZeros(formattedValue);
+parsePosition.setIndex(parsePosition.getIndex() + 2);
+ }
+
+ if (ordinalSuffix != null) {
+String suffix;
+
+if (formattedValue.length() >= 2
+&& formattedValue.charAt(formattedValue.length() - 2) == '1') {
+
Re: [PR] [CALCITE-6358] Support all PostgreSQL 14 date/time patterns [calcite]
normanj-bitquill commented on code in PR #3773:
URL: https://github.com/apache/calcite/pull/3773#discussion_r1583385179
##
core/src/main/java/org/apache/calcite/util/format/PostgresqlDateTimeFormatter.java:
##
@@ -0,0 +1,660 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to you under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.calcite.util.format;
+
+import java.text.ParsePosition;
+import java.time.Month;
+import java.time.ZonedDateTime;
+import java.time.format.TextStyle;
+import java.time.temporal.ChronoField;
+import java.time.temporal.IsoFields;
+import java.time.temporal.JulianFields;
+import java.util.Locale;
+import java.util.function.Function;
+
+/**
+ * Provides an implementation of toChar that matches PostgreSQL behaviour.
+ */
+public class PostgresqlDateTimeFormatter {
+ /**
+ * Result of applying a format element to the current position in the format
+ * string. If matched, will contain the output from applying the format
+ * element.
+ */
+ private static class PatternConvertResult {
+final boolean matched;
+final String formattedString;
+
+protected PatternConvertResult() {
+ matched = false;
+ formattedString = "";
+}
+
+protected PatternConvertResult(boolean matched, String formattedString) {
+ this.matched = matched;
+ this.formattedString = formattedString;
+}
+ }
+
+ /**
+ * A format element that is able to produce a string from a date.
+ */
+ private interface FormatPattern {
+/**
+ * Checks if the current position in the format string applies to this
format
+ * element. Will produce a formatted string if matched that is the format
element
+ * applied to the datetime. If matched will also update the parser
position.
+ *
+ * @param parsePosition current position in the format string
+ * @param formatString input format string
+ * @param dateTime datetime to convert
+ * @return the string representation of the datetime based on the format
pattern
+ */
+PatternConvertResult convert(ParsePosition parsePosition, String
formatString,
+ZonedDateTime dateTime);
+ }
+
+ /**
+ * A format element that will produce a number. Nubmers can have leading
zeroes
+ * removed and can have ordinal suffixes.
+ */
+ private static class NumberFormatPattern implements FormatPattern {
+private final String[] patterns;
+private final Function converter;
+
+protected NumberFormatPattern(Function converter,
String... patterns) {
+ this.converter = converter;
+ this.patterns = patterns;
+}
+
+@Override public PatternConvertResult convert(ParsePosition parsePosition,
String formatString,
+ZonedDateTime dateTime) {
+ final String formatStringTrimmed =
formatString.substring(parsePosition.getIndex());
+
+ String patternToUse = null;
+ for (String pattern : patterns) {
+if (formatStringTrimmed.startsWith(pattern)
+|| formatStringTrimmed.startsWith("FM" + pattern)) {
+ patternToUse = pattern;
+ break;
+}
+ }
+
+ if (patternToUse == null) {
+return NO_PATTERN_MATCH;
+ }
+
+ parsePosition.setIndex(parsePosition.getIndex() + patternToUse.length());
+
+ boolean fillMode = !formatStringTrimmed.startsWith("FM");
+ String ordinalSuffix = null;
+ if (formatStringTrimmed.startsWith("FM" + patternToUse + "TH")) {
+fillMode = false;
+ordinalSuffix = "TH";
+ } else if (formatStringTrimmed.startsWith("FM" + patternToUse + "th")) {
+fillMode = false;
+ordinalSuffix = "th";
+ } else if (formatStringTrimmed.startsWith(patternToUse + "TH")) {
+ordinalSuffix = "TH";
+ } else if (formatStringTrimmed.startsWith(patternToUse + "th")) {
+ordinalSuffix = "th";
+ }
+
+ String formattedValue = converter.apply(dateTime);
+ if (!fillMode) {
+formattedValue = trimLeadingZeros(formattedValue);
+parsePosition.setIndex(parsePosition.getIndex() + 2);
+ }
+
+ if (ordinalSuffix != null) {
+String suffix;
+
+if (formattedValue.length() >= 2
+&& formattedValue.charAt(formattedValue.length() - 2) == '1') {
+
Re: [PR] [CALCITE-6358] Support all PostgreSQL 14 date/time patterns [calcite]
normanj-bitquill commented on code in PR #3773:
URL: https://github.com/apache/calcite/pull/3773#discussion_r1583378225
##
core/src/test/java/org/apache/calcite/util/format/PostgresqlDateTimeFormatterTest.java:
##
@@ -0,0 +1,1238 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to you under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.calcite.util.format;
+
+import org.junit.jupiter.api.Test;
+import org.junit.jupiter.params.ParameterizedTest;
+import org.junit.jupiter.params.provider.ValueSource;
+
+import java.time.LocalDateTime;
+import java.time.ZoneId;
+import java.time.ZonedDateTime;
+import java.util.Locale;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+
+/**
+ * Unit test for {@link PostgresqlDateTimeFormatter}.
+ */
+public class PostgresqlDateTimeFormatterTest {
+ @ParameterizedTest
+ @ValueSource(strings = {"HH12", "HH"})
+ void testHH12(String pattern) {
+final ZonedDateTime midnight = createDateTime(2024, 1, 1, 0, 0, 0, 0);
+final ZonedDateTime morning = createDateTime(2024, 1, 1, 6, 0, 0, 0);
+final ZonedDateTime noon = createDateTime(2024, 1, 1, 12, 0, 0, 0);
+final ZonedDateTime evening = createDateTime(2024, 1, 1, 18, 0, 0, 0);
+
+assertEquals("12", PostgresqlDateTimeFormatter.toChar(pattern, midnight));
+assertEquals("06", PostgresqlDateTimeFormatter.toChar(pattern, morning));
+assertEquals("12", PostgresqlDateTimeFormatter.toChar(pattern, noon));
+assertEquals("06", PostgresqlDateTimeFormatter.toChar(pattern, evening));
+assertEquals(
+"12", PostgresqlDateTimeFormatter.toChar("FM" + pattern,
+midnight));
+assertEquals(
+"6", PostgresqlDateTimeFormatter.toChar("FM" + pattern,
+morning));
+assertEquals(
+"12", PostgresqlDateTimeFormatter.toChar("FM" + pattern,
+noon));
+assertEquals(
+"6", PostgresqlDateTimeFormatter.toChar("FM" + pattern,
+evening));
+
+final ZonedDateTime hourOne = createDateTime(2024, 1, 1, 1, 0, 0, 0);
+final ZonedDateTime hourTwo = createDateTime(2024, 1, 1, 2, 0, 0, 0);
+final ZonedDateTime hourThree = createDateTime(2024, 1, 1, 3, 0, 0, 0);
+assertEquals(
+"12TH", PostgresqlDateTimeFormatter.toChar(pattern + "TH",
+midnight));
+assertEquals(
+"01ST", PostgresqlDateTimeFormatter.toChar(pattern + "TH",
+hourOne));
+assertEquals(
+"02ND", PostgresqlDateTimeFormatter.toChar(pattern + "TH",
+hourTwo));
+assertEquals(
+"03RD", PostgresqlDateTimeFormatter.toChar(pattern + "TH",
+hourThree));
+assertEquals(
+"12th", PostgresqlDateTimeFormatter.toChar(pattern + "th",
+midnight));
+assertEquals(
+"01st", PostgresqlDateTimeFormatter.toChar(pattern + "th",
+hourOne));
+assertEquals(
+"02nd", PostgresqlDateTimeFormatter.toChar(pattern + "th",
+hourTwo));
+assertEquals(
+"03rd", PostgresqlDateTimeFormatter.toChar(pattern + "th",
+hourThree));
+
+assertEquals(
+"2nd", PostgresqlDateTimeFormatter.toChar(
+"FM" + pattern + "th", hourTwo));
+ }
+
+ @Test void testHH24() {
+final ZonedDateTime midnight = createDateTime(2024, 1, 1, 0, 0, 0, 0);
+final ZonedDateTime morning = createDateTime(2024, 1, 1, 6, 0, 0, 0);
+final ZonedDateTime noon = createDateTime(2024, 1, 1, 12, 0, 0, 0);
+final ZonedDateTime evening = createDateTime(2024, 1, 1, 18, 0, 0, 0);
+
+assertEquals("00", PostgresqlDateTimeFormatter.toChar("HH24", midnight));
+assertEquals("06", PostgresqlDateTimeFormatter.toChar("HH24", morning));
+assertEquals("12", PostgresqlDateTimeFormatter.toChar("HH24", noon));
+assertEquals("18", PostgresqlDateTimeFormatter.toChar("HH24", evening));
+assertEquals("0", PostgresqlDateTimeFormatter.toChar("FMHH24", midnight));
+assertEquals("6", PostgresqlDateTimeFormatter.toChar("FMHH24", morning));
+assertEquals("12", PostgresqlDateTimeFormatter.toChar("FMHH24", noon));
+assertEquals("18", PostgresqlDateTimeFormatter.toChar("FMHH24", evening));
+
+final ZonedDateTime hourOne = createDateTime(2024, 1, 1, 1, 0, 0, 0);
+final ZonedDateTime hourTwo = createDateTime(2024, 1, 1, 2, 0,
Re: [PR] [CALCITE-6358] Support all PostgreSQL 14 date/time patterns [calcite]
normanj-bitquill commented on code in PR #3773: URL: https://github.com/apache/calcite/pull/3773#discussion_r1583376456 ## babel/src/test/resources/sql/postgresql.iq: ## @@ -58,11 +58,341 @@ NAME, PAY_BY_QUARTER, SCHEDULE Bill, [1, 1, 1, 1], [[meeting, lunch], [training, presentation]] !ok -select to_char(timestamp '2022-06-03 12:15:48.678', '-MM-DD HH24:MI:SS.MS TZ'); +select to_char(timestamp '2022-06-03 12:15:48.678', '-MM-DD HH24:MI:SS.MS'); EXPR$0 2022-06-03 12:15:48.678 !ok +select to_char(timestamp '2022-06-03 12:15:48.678', 'HH'); +EXPR$0 +12 +!ok + +select to_char(timestamp '2022-06-03 13:15:48.678', 'HH12'); +EXPR$0 +01 +!ok + +select to_char(timestamp '2022-06-03 13:15:48.678', 'HH24'); +EXPR$0 +13 +!ok + +select to_char(timestamp '2022-06-03 13:15:48.678', 'MI'); +EXPR$0 +15 +!ok + +select to_char(timestamp '2022-06-03 13:15:48.678', 'SS'); +EXPR$0 +48 +!ok + +select to_char(timestamp '2022-06-03 13:15:48.678', 'MS'); +EXPR$0 +678 +!ok + +select to_char(timestamp '2022-06-03 12:15:48.678', 'US'); +EXPR$0 +678000 +!ok + +select to_char(timestamp '2022-06-03 12:15:48.678', 'FF1'); +EXPR$0 +6 +!ok + +select to_char(timestamp '2022-06-03 12:15:48.678', 'FF2'); +EXPR$0 +67 +!ok + +select to_char(timestamp '2022-06-03 12:15:48.678', 'FF3'); +EXPR$0 +678 +!ok + +select to_char(timestamp '2022-06-03 12:15:48.678', 'FF4'); +EXPR$0 +6780 +!ok + +select to_char(timestamp '2022-06-03 12:15:48.678', 'FF5'); +EXPR$0 +67800 +!ok + +select to_char(timestamp '2022-06-03 12:15:48.678', 'FF6'); +EXPR$0 +678000 +!ok + +select to_char(timestamp '2022-06-03 12:15:48.678', ''); +EXPR$0 +44148 +!ok + +select to_char(timestamp '2022-06-03 12:15:48.678', 'S'); +EXPR$0 +44148 +!ok + +select to_char(timestamp '2022-06-03 12:15:48.678', 'AM'); +EXPR$0 +PM +!ok + +select to_char(timestamp '2022-06-03 12:15:48.678', 'am'); +EXPR$0 +pm +!ok + +select to_char(timestamp '2022-06-03 02:15:48.678', 'PM'); +EXPR$0 +AM +!ok + +select to_char(timestamp '2022-06-03 02:15:48.678', 'pm'); +EXPR$0 +am +!ok + +select to_char(timestamp '2022-06-03 12:15:48.678', 'A.M.'); +EXPR$0 +P.M. +!ok + +select to_char(timestamp '2022-06-03 12:15:48.678', 'a.m.'); +EXPR$0 +p.m. +!ok + +select to_char(timestamp '2022-06-03 02:15:48.678', 'P.M.'); +EXPR$0 +A.M. +!ok + +select to_char(timestamp '2022-06-03 02:15:48.678', 'p.m.'); +EXPR$0 +a.m. +!ok + +select to_char(timestamp '2022-06-03 12:15:48.678', 'Y,YYY'); +EXPR$0 +2,022 +!ok + +select to_char(timestamp '2022-06-03 12:15:48.678', ''); +EXPR$0 +2022 +!ok + +select to_char(timestamp '2022-06-03 12:15:48.678', 'YYY'); +EXPR$0 +022 +!ok + +select to_char(timestamp '2022-06-03 12:15:48.678', 'YY'); +EXPR$0 +22 +!ok + +select to_char(timestamp '2022-06-03 12:15:48.678', 'Y'); +EXPR$0 +2 +!ok + +select to_char(timestamp '2023-01-01 12:15:48.678', 'IYYY'); +EXPR$0 +2022 +!ok + +select to_char(timestamp '2023-01-01 12:15:48.678', 'IYY'); +EXPR$0 +022 +!ok + +select to_char(timestamp '2023-01-01 12:15:48.678', 'IY'); +EXPR$0 +22 +!ok + +select to_char(timestamp '2023-01-01 12:15:48.678', 'I'); +EXPR$0 +2 +!ok + +select to_char(timestamp '2022-06-03 12:15:48.678', 'BC'); +EXPR$0 +AD +!ok + +select to_char(timestamp '2022-06-03 12:15:48.678', 'bc'); +EXPR$0 +ad +!ok + +select to_char(timestamp '2022-06-03 12:15:48.678', 'AD'); +EXPR$0 +AD +!ok + +select to_char(timestamp '2022-06-03 12:15:48.678', 'ad'); +EXPR$0 +ad +!ok + +select to_char(timestamp '2022-06-03 12:15:48.678', 'B.C.'); +EXPR$0 +A.D. +!ok + +select to_char(timestamp '2022-06-03 12:15:48.678', 'b.c.'); +EXPR$0 +a.d. +!ok + +select to_char(timestamp '2022-06-03 12:15:48.678', 'A.D.'); +EXPR$0 +A.D. +!ok + +select to_char(timestamp '2022-06-03 12:15:48.678', 'a.d.'); +EXPR$0 +a.d. +!ok + +select to_char(timestamp '2022-06-03 12:15:48.678', 'MONTH'); +EXPR$0 +JUNE +!ok + +select to_char(timestamp '2022-06-03 12:15:48.678', 'Month'); +EXPR$0 +June +!ok + +select to_char(timestamp '2022-06-03 12:15:48.678', 'month'); +EXPR$0 +june +!ok + +select to_char(timestamp '2022-06-03 12:15:48.678', 'MON'); +EXPR$0 +JUN +!ok + +select to_char(timestamp '2022-06-03 12:15:48.678', 'Mon'); +EXPR$0 +Jun +!ok + +select to_char(timestamp '2022-06-03 12:15:48.678', 'mon'); +EXPR$0 +jun +!ok + +select to_char(timestamp '2022-06-03 12:15:48.678', 'DAY'); +EXPR$0 +FRIDAY +!ok + +select to_char(timestamp '2022-06-03 12:15:48.678', 'Day'); +EXPR$0 +Friday +!ok + +select to_char(timestamp '2022-06-03 12:15:48.678', 'day'); +EXPR$0 +friday +!ok + +select to_char(timestamp '2022-06-03 12:15:48.678', 'DY'); +EXPR$0 +FRI +!ok + +select to_char(timestamp '0001-01-01 00:00:00.000', 'DY'); +EXPR$0 +MON +!ok + +select to_char(timestamp '2022-06-03 12:15:48.678', 'Dy'); +EXPR$0 +Fri +!ok + +select to_char(timestamp '2022-06-03 12:15:48.678', 'dy'); +EXPR$0 +fri +!ok + +select to_char(timestamp '2022-06-03 12:15:48.678', 'DDD'); +EXPR$0 +154 +!ok + +select to_char(timestamp '2022-06-03 12:15:48.678', 'IDDD');
Re: [PR] [CALCITE-6358] Support all PostgreSQL 14 date/time patterns [calcite]
mihaibudiu commented on PR #3773: URL: https://github.com/apache/calcite/pull/3773#issuecomment-2081261317 I have approved, but also left some questions. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org
Re: [PR] [CALCITE-6358] Support all PostgreSQL 14 date/time patterns [calcite]
mihaibudiu commented on code in PR #3773:
URL: https://github.com/apache/calcite/pull/3773#discussion_r1581975923
##
core/src/main/java/org/apache/calcite/util/format/PostgresqlDateTimeFormatter.java:
##
@@ -0,0 +1,660 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to you under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.calcite.util.format;
+
+import java.text.ParsePosition;
+import java.time.Month;
+import java.time.ZonedDateTime;
+import java.time.format.TextStyle;
+import java.time.temporal.ChronoField;
+import java.time.temporal.IsoFields;
+import java.time.temporal.JulianFields;
+import java.util.Locale;
+import java.util.function.Function;
+
+/**
+ * Provides an implementation of toChar that matches PostgreSQL behaviour.
+ */
+public class PostgresqlDateTimeFormatter {
+ /**
+ * Result of applying a format element to the current position in the format
+ * string. If matched, will contain the output from applying the format
+ * element.
+ */
+ private static class PatternConvertResult {
+final boolean matched;
+final String formattedString;
+
+protected PatternConvertResult() {
+ matched = false;
+ formattedString = "";
+}
+
+protected PatternConvertResult(boolean matched, String formattedString) {
+ this.matched = matched;
+ this.formattedString = formattedString;
+}
+ }
+
+ /**
+ * A format element that is able to produce a string from a date.
+ */
+ private interface FormatPattern {
+/**
+ * Checks if the current position in the format string applies to this
format
+ * element. Will produce a formatted string if matched that is the format
element
Review Comment:
I find the second phrase difficult to parse,maybe you can rephrase it.
##
babel/src/test/resources/sql/postgresql.iq:
##
@@ -58,11 +58,341 @@ NAME, PAY_BY_QUARTER, SCHEDULE
Bill, [1, 1, 1, 1], [[meeting, lunch], [training,
presentation]]
!ok
-select to_char(timestamp '2022-06-03 12:15:48.678', '-MM-DD HH24:MI:SS.MS
TZ');
+select to_char(timestamp '2022-06-03 12:15:48.678', '-MM-DD
HH24:MI:SS.MS');
EXPR$0
2022-06-03 12:15:48.678
!ok
+select to_char(timestamp '2022-06-03 12:15:48.678', 'HH');
+EXPR$0
+12
+!ok
+
+select to_char(timestamp '2022-06-03 13:15:48.678', 'HH12');
+EXPR$0
+01
+!ok
+
+select to_char(timestamp '2022-06-03 13:15:48.678', 'HH24');
+EXPR$0
+13
+!ok
+
+select to_char(timestamp '2022-06-03 13:15:48.678', 'MI');
+EXPR$0
+15
+!ok
+
+select to_char(timestamp '2022-06-03 13:15:48.678', 'SS');
+EXPR$0
+48
+!ok
+
+select to_char(timestamp '2022-06-03 13:15:48.678', 'MS');
+EXPR$0
+678
+!ok
+
+select to_char(timestamp '2022-06-03 12:15:48.678', 'US');
+EXPR$0
+678000
+!ok
+
+select to_char(timestamp '2022-06-03 12:15:48.678', 'FF1');
+EXPR$0
+6
+!ok
+
+select to_char(timestamp '2022-06-03 12:15:48.678', 'FF2');
+EXPR$0
+67
+!ok
+
+select to_char(timestamp '2022-06-03 12:15:48.678', 'FF3');
+EXPR$0
+678
+!ok
+
+select to_char(timestamp '2022-06-03 12:15:48.678', 'FF4');
+EXPR$0
+6780
+!ok
+
+select to_char(timestamp '2022-06-03 12:15:48.678', 'FF5');
+EXPR$0
+67800
+!ok
+
+select to_char(timestamp '2022-06-03 12:15:48.678', 'FF6');
+EXPR$0
+678000
+!ok
+
+select to_char(timestamp '2022-06-03 12:15:48.678', '');
+EXPR$0
+44148
+!ok
+
+select to_char(timestamp '2022-06-03 12:15:48.678', 'S');
+EXPR$0
+44148
+!ok
+
+select to_char(timestamp '2022-06-03 12:15:48.678', 'AM');
+EXPR$0
+PM
+!ok
+
+select to_char(timestamp '2022-06-03 12:15:48.678', 'am');
+EXPR$0
+pm
+!ok
+
+select to_char(timestamp '2022-06-03 02:15:48.678', 'PM');
+EXPR$0
+AM
+!ok
+
+select to_char(timestamp '2022-06-03 02:15:48.678', 'pm');
+EXPR$0
+am
+!ok
+
+select to_char(timestamp '2022-06-03 12:15:48.678', 'A.M.');
+EXPR$0
+P.M.
+!ok
+
+select to_char(timestamp '2022-06-03 12:15:48.678', 'a.m.');
+EXPR$0
+p.m.
+!ok
+
+select to_char(timestamp '2022-06-03 02:15:48.678', 'P.M.');
+EXPR$0
+A.M.
+!ok
+
+select to_char(timestamp '2022-06-03 02:15:48.678', 'p.m.');
+EXPR$0
+a.m.
+!ok
+
+select to_char(timestamp '2022-06-03 12:15:48.678', 'Y,YYY');
+EXPR$0
+2,022
+!ok
+
+select to_char(timestamp '2022-06-03 12:15:48.678', '');
+EXPR$0
+2022
+!ok
+
+select to_char(timestamp '2022-06-03
Re: [PR] [CALCITE-6358] Support all PostgreSQL 14 date/time patterns [calcite]
mihaibudiu commented on PR #3773: URL: https://github.com/apache/calcite/pull/3773#issuecomment-2080050465 The statement that they were run against Postgres helps a lot. Thanks! -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org
Re: [PR] [CALCITE-6358] Support all PostgreSQL 14 date/time patterns [calcite]
normanj-bitquill commented on PR #3773: URL: https://github.com/apache/calcite/pull/3773#issuecomment-2080047785 @mihaibudiu > The amount of testing is very impressive. These won't be easy to review. I assume they were checked against postgres. You should address the failures reported by the CI. These were tested against PostgreSQL 14. The CI issues have been addressed. Is there anything that I can be done to make reviewing this easier? -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org
Re: [PR] [CALCITE-6358] Support all PostgreSQL 14 date/time patterns [calcite]
sonarcloud[bot] commented on PR #3773: URL: https://github.com/apache/calcite/pull/3773#issuecomment-2079841077 ## [](https://sonarcloud.io/dashboard?id=apache_calcite=3773) **Quality Gate passed** Issues  [10 New issues](https://sonarcloud.io/project/issues?id=apache_calcite=3773=false=true)  [0 Accepted issues](https://sonarcloud.io/component_measures?id=apache_calcite=3773=new_accepted_issues=list) Measures  [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite=3773=false=true)  [87.0% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_calcite=3773=new_coverage=list)  [0.0% Duplication on New Code](https://sonarcloud.io/component_measures?id=apache_calcite=3773=new_duplicated_lines_density=list) [See analysis details on SonarCloud](https://sonarcloud.io/dashboard?id=apache_calcite=3773) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org
Re: [PR] [CALCITE-6358] Support all PostgreSQL 14 date/time patterns [calcite]
sonarcloud[bot] commented on PR #3773: URL: https://github.com/apache/calcite/pull/3773#issuecomment-2079818233 ## [](https://sonarcloud.io/dashboard?id=apache_calcite=3773) **Quality Gate passed** Issues  [10 New issues](https://sonarcloud.io/project/issues?id=apache_calcite=3773=false=true)  [0 Accepted issues](https://sonarcloud.io/component_measures?id=apache_calcite=3773=new_accepted_issues=list) Measures  [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite=3773=false=true)  [87.0% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_calcite=3773=new_coverage=list)  [0.0% Duplication on New Code](https://sonarcloud.io/component_measures?id=apache_calcite=3773=new_duplicated_lines_density=list) [See analysis details on SonarCloud](https://sonarcloud.io/dashboard?id=apache_calcite=3773) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org
Re: [PR] [CALCITE-6358] Support all PostgreSQL 14 date/time patterns [calcite]
sonarcloud[bot] commented on PR #3773: URL: https://github.com/apache/calcite/pull/3773#issuecomment-2078316741 ## [](https://sonarcloud.io/dashboard?id=apache_calcite=3773) **Quality Gate passed** Issues  [10 New issues](https://sonarcloud.io/project/issues?id=apache_calcite=3773=false=true)  [0 Accepted issues](https://sonarcloud.io/component_measures?id=apache_calcite=3773=new_accepted_issues=list) Measures  [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite=3773=false=true)  [87.1% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_calcite=3773=new_coverage=list)  [28.1% Duplication on New Code](https://sonarcloud.io/component_measures?id=apache_calcite=3773=new_duplicated_lines_density=list) [See analysis details on SonarCloud](https://sonarcloud.io/dashboard?id=apache_calcite=3773) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org
Re: [PR] [CALCITE-6358] Support all PostgreSQL 14 date/time patterns [calcite]
sonarcloud[bot] commented on PR #3773: URL: https://github.com/apache/calcite/pull/3773#issuecomment-2078270694 ## [](https://sonarcloud.io/dashboard?id=apache_calcite=3773) **Quality Gate passed** Issues  [10 New issues](https://sonarcloud.io/project/issues?id=apache_calcite=3773=false=true)  [0 Accepted issues](https://sonarcloud.io/component_measures?id=apache_calcite=3773=new_accepted_issues=list) Measures  [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite=3773=false=true)  [86.9% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_calcite=3773=new_coverage=list)  [28.3% Duplication on New Code](https://sonarcloud.io/component_measures?id=apache_calcite=3773=new_duplicated_lines_density=list) [See analysis details on SonarCloud](https://sonarcloud.io/dashboard?id=apache_calcite=3773) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org
Re: [PR] [CALCITE-6358] Support all PostgreSQL 14 date/time patterns [calcite]
sonarcloud[bot] commented on PR #3773: URL: https://github.com/apache/calcite/pull/3773#issuecomment-2078232952 ## [](https://sonarcloud.io/dashboard?id=apache_calcite=3773) **Quality Gate passed** Issues  [10 New issues](https://sonarcloud.io/project/issues?id=apache_calcite=3773=false=true)  [0 Accepted issues](https://sonarcloud.io/component_measures?id=apache_calcite=3773=new_accepted_issues=list) Measures  [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite=3773=false=true)  [86.7% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_calcite=3773=new_coverage=list)  [0.0% Duplication on New Code](https://sonarcloud.io/component_measures?id=apache_calcite=3773=new_duplicated_lines_density=list) [See analysis details on SonarCloud](https://sonarcloud.io/dashboard?id=apache_calcite=3773) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org
Re: [PR] [CALCITE-6358] Support all PostgreSQL 14 date/time patterns [calcite]
mihaibudiu commented on PR #3773: URL: https://github.com/apache/calcite/pull/3773#issuecomment-2078003367 The amount of testing is very impressive. These won't be easy to review. I assume they were checked against postgres. You should address the failures reported by the CI. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org
Re: [PR] [CALCITE-6358] Support all PostgreSQL 14 date/time patterns [calcite]
normanj-bitquill commented on PR #3773: URL: https://github.com/apache/calcite/pull/3773#issuecomment-207870 Need to properly separate the MySQL/Oracle implementation of `TO_CHAR` from the PostgreSQL implementation. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org
