You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jh...@apache.org on 2019/08/20 22:04:10 UTC

[calcite] 04/06: [CALCITE-3252] Add CONVERT_TIMEZONE, TO_DATE and TO_TIMESTAMP non-standard functions (Lindsey Meyer)

This is an automated email from the ASF dual-hosted git repository.

jhyde pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/calcite.git

commit e8b08c490ab4270945c35f31846cd36b5788cc23
Author: Lindsey Meyer <la...@users.noreply.github.com>
AuthorDate: Wed Aug 14 11:34:15 2019 -0700

    [CALCITE-3252] Add CONVERT_TIMEZONE, TO_DATE and TO_TIMESTAMP non-standard functions (Lindsey Meyer)
    
    CONVERT_TIMEZONE is for Redshift; TO_DATE and TO_TIMESTAMP are for
    PostgreSQL (including Redshift) and Oracle.
    
    Close apache/calcite#1380
---
 .../apache/calcite/sql/SqlJdbcFunctionCall.java    |  7 ++-
 .../calcite/sql/fun/SqlLibraryOperators.java       | 35 ++++++++++++++
 .../org/apache/calcite/sql/type/OperandTypes.java  |  3 ++
 .../calcite/materialize/LatticeSuggesterTest.java  |  8 +++-
 .../org/apache/calcite/test/SqlValidatorTest.java  | 56 ++++++++++++++++++++++
 site/_docs/reference.md                            |  3 ++
 6 files changed, 108 insertions(+), 4 deletions(-)

diff --git a/core/src/main/java/org/apache/calcite/sql/SqlJdbcFunctionCall.java b/core/src/main/java/org/apache/calcite/sql/SqlJdbcFunctionCall.java
index d778908..9757b1f 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlJdbcFunctionCall.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlJdbcFunctionCall.java
@@ -382,9 +382,9 @@ public class SqlJdbcFunctionCall extends SqlFunction {
 
   /** List of all time/date function names defined by JDBC. */
   private static final String TIME_DATE_FUNCTIONS = constructFuncList(
-      "CURDATE", "CURTIME", "DAYNAME", "DAYOFMONTH", "DAYOFWEEK",
+      "CONVERT_TIMEZONE", "CURDATE", "CURTIME", "DAYNAME", "DAYOFMONTH", "DAYOFWEEK",
       "DAYOFYEAR", "HOUR", "MINUTE", "MONTH", "MONTHNAME", "NOW",
-      "QUARTER", "SECOND", "TIMESTAMPADD", "TIMESTAMPDIFF",
+      "QUARTER", "SECOND", "TIMESTAMPADD", "TIMESTAMPDIFF", "TO_DATE", "TO_TIMESTAMP",
       "WEEK", "YEAR");
 
   /** List of all system function names defined by JDBC. */
@@ -729,11 +729,14 @@ public class SqlJdbcFunctionCall extends SqlFunction {
       map.put("MONTHNAME", simple(SqlLibraryOperators.MONTHNAME));
       map.put("SECOND", simple(SqlStdOperatorTable.SECOND));
 
+      map.put("CONVERT_TIMEZONE", simple(SqlLibraryOperators.CONVERT_TIMEZONE));
       map.put("CURDATE", simple(SqlStdOperatorTable.CURRENT_DATE));
       map.put("CURTIME", simple(SqlStdOperatorTable.LOCALTIME));
       map.put("NOW", simple(SqlStdOperatorTable.CURRENT_TIMESTAMP));
       map.put("TIMESTAMPADD", simple(SqlStdOperatorTable.TIMESTAMP_ADD));
       map.put("TIMESTAMPDIFF", simple(SqlStdOperatorTable.TIMESTAMP_DIFF));
+      map.put("TO_DATE", simple(SqlLibraryOperators.TO_DATE));
+      map.put("TO_TIMESTAMP", simple(SqlLibraryOperators.TO_TIMESTAMP));
 
       map.put("DATABASE", simple(SqlStdOperatorTable.CURRENT_CATALOG));
       map.put("IFNULL",
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
index 6acadec..896b9f7 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
@@ -49,6 +49,19 @@ public abstract class SqlLibraryOperators {
   private SqlLibraryOperators() {
   }
 
+  /** The "CONVERT_TIMEZONE(tz1, tz2, datetime)" function;
+   * converts the timezone of {@code datetime} from {@code tz1} to {@code tz2}.
+   * This function is only on Redshift, but we list it in PostgreSQL
+   * because Redshift does not have its own library. */
+  @LibraryOperator(libraries = {POSTGRESQL})
+  public static final SqlFunction CONVERT_TIMEZONE =
+      new SqlFunction("CONVERT_TIMEZONE",
+          SqlKind.OTHER_FUNCTION,
+          ReturnTypes.DATE_NULLABLE,
+          null,
+          OperandTypes.CHARACTER_CHARACTER_DATETIME,
+          SqlFunctionCategory.TIMEDATE);
+
   /** Return type inference for {@code DECODE}. */
   private static final SqlReturnTypeInference DECODE_RETURN_TYPE =
       opBinding -> {
@@ -269,6 +282,28 @@ public abstract class SqlLibraryOperators {
           OperandTypes.or(OperandTypes.STRING, OperandTypes.BINARY),
           SqlFunctionCategory.STRING);
 
+  /** The "TO_DATE(string1, string2)" function; casts string1
+   * to a DATE using the format specified in string2. */
+  @LibraryOperator(libraries = {POSTGRESQL, ORACLE})
+  public static final SqlFunction TO_DATE =
+      new SqlFunction("TO_DATE",
+          SqlKind.OTHER_FUNCTION,
+          ReturnTypes.DATE_NULLABLE,
+          null,
+          OperandTypes.STRING_STRING,
+          SqlFunctionCategory.TIMEDATE);
+
+  /** The "TO_TIMESTAMP(string1, string2)" function; casts string1
+   * to a TIMESTAMP using the format specified in string2. */
+  @LibraryOperator(libraries = {POSTGRESQL, ORACLE})
+  public static final SqlFunction TO_TIMESTAMP =
+      new SqlFunction("TO_TIMESTAMP",
+          SqlKind.OTHER_FUNCTION,
+          ReturnTypes.DATE_NULLABLE,
+          null,
+          OperandTypes.STRING_STRING,
+          SqlFunctionCategory.TIMEDATE);
+
   @LibraryOperator(libraries = {ORACLE})
   public static final SqlFunction CHR =
       new SqlFunction("CHR",
diff --git a/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java b/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java
index ba1cd10..5f24058 100644
--- a/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java
+++ b/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java
@@ -240,6 +240,9 @@ public abstract class OperandTypes {
   public static final SqlSingleOperandTypeChecker INTERVAL =
       family(SqlTypeFamily.DATETIME_INTERVAL);
 
+  public static final SqlSingleOperandTypeChecker CHARACTER_CHARACTER_DATETIME =
+      family(SqlTypeFamily.CHARACTER, SqlTypeFamily.CHARACTER, SqlTypeFamily.DATETIME);
+
   public static final SqlSingleOperandTypeChecker PERIOD =
       new PeriodOperandTypeChecker();
 
diff --git a/core/src/test/java/org/apache/calcite/materialize/LatticeSuggesterTest.java b/core/src/test/java/org/apache/calcite/materialize/LatticeSuggesterTest.java
index d996965..6043eda 100644
--- a/core/src/test/java/org/apache/calcite/materialize/LatticeSuggesterTest.java
+++ b/core/src/test/java/org/apache/calcite/materialize/LatticeSuggesterTest.java
@@ -594,14 +594,18 @@ public class LatticeSuggesterTest {
     assertThat(derivedColumns.get(1).tables, is(tables));
   }
 
-  @Test public void testDialect() throws Exception {
+  @Test public void testRedshiftDialect() throws Exception {
     final Tester t = new Tester().foodmart().withEvolve(true)
         .withDialect(SqlDialect.DatabaseProduct.REDSHIFT.getDialect())
         .withLibrary(SqlLibrary.POSTGRESQL);
 
     final String q0 = "select\n"
-        + "  left(\"fname\", 1) as \"initial\",\n"
         + "  CONCAT(\"fname\", ' ', \"lname\") as \"full_name\",\n"
+        + "  convert_timezone('UTC', 'America/Los_Angeles',\n"
+        + "    cast('2019-01-01 01:00:00' as timestamp)),\n"
+        + "  left(\"fname\", 1) as \"initial\",\n"
+        + "  to_date('2019-01-01', 'YYYY-MM-DD'),\n"
+        + "  to_timestamp('2019-01-01 01:00:00', 'YYYY-MM-DD HH:MM:SS'),\n"
         + "  count(*) as c,\n"
         + "  avg(\"total_children\" - \"num_children_at_home\")\n"
         + "from \"customer\" join \"sales_fact_1997\" using (\"customer_id\")\n"
diff --git a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
index 54e4589..5fe0a66 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -1217,6 +1217,62 @@ public class SqlValidatorTest extends SqlValidatorTestCase {
     checkExp("CAST( '2004-12-21 10:12:21' AS TIMESTAMP)");
   }
 
+  @Test public void testConvertTimezoneFunction() {
+    checkWholeExpFails(
+        "CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', CAST('2000-01-01' AS TIMESTAMP))",
+        "No match found for function signature CONVERT_TIMEZONE\\(<CHARACTER>, <CHARACTER>, <TIMESTAMP>\\)");
+    tester = tester.withOperatorTable(
+        SqlLibraryOperatorTableFactory.INSTANCE
+            .getOperatorTable(SqlLibrary.STANDARD, SqlLibrary.POSTGRESQL));
+    checkExpType(
+        "CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', CAST('2000-01-01' AS TIMESTAMP))",
+        "DATE NOT NULL");
+    checkWholeExpFails("CONVERT_TIMEZONE('UTC', 'America/Los_Angeles')",
+        "Invalid number of arguments to function 'CONVERT_TIMEZONE'. Was expecting 3 arguments");
+    checkWholeExpFails("CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', '2000-01-01')",
+        "Cannot apply 'CONVERT_TIMEZONE' to arguments of type 'CONVERT_TIMEZONE\\(<CHAR\\(3\\)>, <CHAR\\(19\\)>, "
+            + "<CHAR\\(10\\)>\\)'\\. Supported form\\(s\\): 'CONVERT_TIMEZONE\\(<CHARACTER>, <CHARACTER>, <DATETIME>\\)'");
+    checkWholeExpFails("CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', "
+            + "'UTC', CAST('2000-01-01' AS TIMESTAMP))",
+        "Invalid number of arguments to function 'CONVERT_TIMEZONE'. Was expecting 3 arguments");
+  }
+
+  @Test public void testToDateFunction() {
+    checkWholeExpFails(
+        "TO_DATE('2000-01-01', 'YYYY-MM-DD')",
+        "No match found for function signature TO_DATE\\(<CHARACTER>, <CHARACTER>\\)");
+    tester = tester.withOperatorTable(
+        SqlLibraryOperatorTableFactory.INSTANCE
+            .getOperatorTable(SqlLibrary.STANDARD, SqlLibrary.POSTGRESQL));
+    checkExpType("TO_DATE('2000-01-01', 'YYYY-MM-DD')",
+        "DATE NOT NULL");
+    checkWholeExpFails("TO_DATE('2000-01-01')",
+        "Invalid number of arguments to function 'TO_DATE'. Was expecting 2 arguments");
+    checkWholeExpFails("TO_DATE(2000, 'YYYY')",
+        "Cannot apply 'TO_DATE' to arguments of type 'TO_DATE\\(<INTEGER>, <CHAR\\(4\\)>\\)'\\. "
+            + "Supported form\\(s\\): 'TO_DATE\\(<STRING>, <STRING>\\)'");
+    checkWholeExpFails("TO_DATE('2000-01-01', 'YYYY-MM-DD', 'YYYY-MM-DD')",
+        "Invalid number of arguments to function 'TO_DATE'. Was expecting 2 arguments");
+  }
+
+  @Test public void testToTimestampFunction() {
+    checkWholeExpFails(
+        "TO_TIMESTAMP('2000-01-01 01:00:00', 'YYYY-MM-DD HH:MM:SS')",
+        "No match found for function signature TO_TIMESTAMP\\(<CHARACTER>, <CHARACTER>\\)");
+    tester = tester.withOperatorTable(
+        SqlLibraryOperatorTableFactory.INSTANCE
+            .getOperatorTable(SqlLibrary.STANDARD, SqlLibrary.POSTGRESQL));
+    checkExpType("TO_TIMESTAMP('2000-01-01 01:00:00', 'YYYY-MM-DD HH:MM:SS')",
+        "DATE NOT NULL");
+    checkWholeExpFails("TO_TIMESTAMP('2000-01-01 01:00:00')",
+        "Invalid number of arguments to function 'TO_TIMESTAMP'. Was expecting 2 arguments");
+    checkWholeExpFails("TO_TIMESTAMP(2000, 'YYYY')",
+        "Cannot apply 'TO_TIMESTAMP' to arguments of type 'TO_TIMESTAMP\\(<INTEGER>, <CHAR\\(4\\)>\\)'\\. "
+            + "Supported form\\(s\\): 'TO_TIMESTAMP\\(<STRING>, <STRING>\\)'");
+    checkWholeExpFails("TO_TIMESTAMP('2000-01-01 01:00:00', 'YYYY-MM-DD HH:MM:SS', 'YYYY-MM-DD')",
+        "Invalid number of arguments to function 'TO_TIMESTAMP'. Was expecting 2 arguments");
+  }
+
   @Test public void testInvalidFunction() {
     checkWholeExpFails("foo()", "No match found for function signature FOO..");
     checkWholeExpFails("mod(123)",
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 327dac4..72de0f5 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -2173,6 +2173,7 @@ semantics.
 | p | expr :: type                                   | Casts *expr* to *type*
 | o | CHR(integer) | Returns the character having the binary equivalent to *integer* as a CHAR value
 | m o p | CONCAT(string [, string ]*)                | Concatenates two or more strings
+| p | CONVERT_TIMEZONE(tz1, tz2, datetime)           | Converts the timezone of *datetime* from *tz1* to *tz2*
 | o | DECODE(value, value1, result1 [, valueN, resultN ]* [, default ]) | Compares *value* to each *valueN* value one by one; if *value* is equal to a *valueN*, returns the corresponding *resultN*, else returns *default*, or NULL if *default* is not specified
 | p | DIFFERENCE(string, string)                     | Returns a measure of the similarity of two strings, namely the number of character positions that their `SOUNDEX` values have in common: 4 if the `SOUNDEX` values are same and 0 if the `SOUNDEX` values are totally different
 | o | GREATEST(expr [, expr ]*)                      | Returns the greatest of the expressions
@@ -2198,6 +2199,8 @@ semantics.
 | m o p | SOUNDEX(string)                            | Returns the phonetic representation of *string*; throws if *string* is encoded with multi-byte encoding such as UTF-8
 | m | SPACE(integer)                                 | Returns a string of *integer* spaces; returns an empty string if *integer* is less than 1
 | o | SUBSTR(string, position [, substring_length ]) | Returns a portion of *string*, beginning at character *position*, *substring_length* characters long. SUBSTR calculates lengths using characters as defined by the input character set
+| o p | TO_DATE(string, format)                      | Converts *string* to a date using the format *format*
+| o p | TO_TIMESTAMP(string, format)                 | Converts *string* to a timestamp using the format *format*
 | o p | TRANSLATE(expr, fromString, toString)        | Returns *expr* with all occurrences of each character in *fromString* replaced by its corresponding character in *toString*. Characters in *expr* that are not in *fromString* are not replaced
 
 Note: