This is an automated email from the ASF dual-hosted git repository.
alexpl pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/ignite.git
The following commit(s) were added to refs/heads/master by this push:
new c1e0332085e IGNITE-23684 SQL Calcite: Add date formatting/parsing
functions with custom format - Fixes #11674.
c1e0332085e is described below
commit c1e0332085e3674a179bc4e9d1a6ab1c4f0ec54b
Author: Aleksey Plekhanov <[email protected]>
AuthorDate: Fri Nov 29 09:45:43 2024 +0300
IGNITE-23684 SQL Calcite: Add date formatting/parsing functions with custom
format - Fixes #11674.
Signed-off-by: Aleksey Plekhanov <[email protected]>
---
docs/_docs/SQL/sql-calcite.adoc | 2 +-
.../internal/processors/query/calcite/exec/exp/RexImpTable.java | 6 ++++++
.../processors/query/calcite/sql/fun/IgniteStdSqlOperatorTable.java | 3 +++
.../processors/query/calcite/integration/StdSqlOperatorsTest.java | 3 +++
4 files changed, 13 insertions(+), 1 deletion(-)
diff --git a/docs/_docs/SQL/sql-calcite.adoc b/docs/_docs/SQL/sql-calcite.adoc
index 298f4a5c28b..2e3ddc766e8 100644
--- a/docs/_docs/SQL/sql-calcite.adoc
+++ b/docs/_docs/SQL/sql-calcite.adoc
@@ -153,7 +153,7 @@ The Calcite-based SQL engine currently supports:
|`MOD`, `EXP`, `POWER`, `LN`, `LOG10`, `ABS`, `RAND`, `RAND_INTEGER`, `ACOS`,
`ACOSH`, `ASIN`, `ASINH`, `ATAN`, `ATANH`, `ATAN2`, `SQRT`, `CBRT`, `COS`,
`COSH`, `COT`, `COTH`, `DEGREES`, `RADIANS`, `ROUND`, `SIGN`, `SIN`, `SINH`,
`TAN`, `TANH`, `SEC`, `SECH`, `CSC`, `CSCH`, `TRUNCATE`, `PI`, `BITAND`,
`BITOR`, `BITXOR`
|Date and time functions
-|`EXTRACT`, `FLOOR`, `CEIL`, `TIMESTAMPADD`, `TIMESTAMPDIFF`, `LAST_DATE`,
`DAYNAME`, `MONTHNAME`, `DAYOFMONTH`, `DAYOFWEEK`, `DAYOFYEAR`, `YEAR`,
`QUARTER`, `MONTH`, `WEEK`, `HOUR`, `MINUTE`, `SECOND`, `TIMESTAMP_SECONDS`,
`TIMESTAMP_MILLIS`, `TIMESTAMP_MICROS`, `UNIX_SECONDS`, `UNIX_MILLIS`,
`UNIX_MICROS`, `UNIX_DATE`, `DATE_FROM_UNIX_DATE`, `DATE`, `TIME`, `DATETIME`,
`CURRENT_TIME`, `CURRENT_TIMESTAMP`, `CURRENT_DATE`, `LOCALTIME`,
`LOCALTIMESTAMP`
+|`EXTRACT`, `FLOOR`, `CEIL`, `TIMESTAMPADD`, `TIMESTAMPDIFF`, `LAST_DATE`,
`DAYNAME`, `MONTHNAME`, `DAYOFMONTH`, `DAYOFWEEK`, `DAYOFYEAR`, `YEAR`,
`QUARTER`, `MONTH`, `WEEK`, `HOUR`, `MINUTE`, `SECOND`, `TIMESTAMP_SECONDS`,
`TIMESTAMP_MILLIS`, `TIMESTAMP_MICROS`, `UNIX_SECONDS`, `UNIX_MILLIS`,
`UNIX_MICROS`, `UNIX_DATE`, `DATE_FROM_UNIX_DATE`, `DATE`, `TIME`, `DATETIME`,
`CURRENT_TIME`, `CURRENT_TIMESTAMP`, `CURRENT_DATE`, `LOCALTIME`,
`LOCALTIMESTAMP`, `TO_CHAR`, `TO_DATE`, `TO_TIMESTAMP`
|XML functions
|`EXTRACTVALUE`, `XMLTRANSFORM`, `EXTRACT`, `EXISTSNODE`
diff --git
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/RexImpTable.java
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/RexImpTable.java
index c66eae98219..cd3a5752f94 100644
---
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/RexImpTable.java
+++
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/RexImpTable.java
@@ -135,6 +135,9 @@ import static
org.apache.calcite.sql.fun.SqlLibraryOperators.TIMESTAMP_MICROS;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.TIMESTAMP_MILLIS;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.TIMESTAMP_SECONDS;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.TO_BASE64;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.TO_CHAR;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.TO_DATE;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.TO_TIMESTAMP;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.TRANSLATE3;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.UNIX_DATE;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.UNIX_MICROS;
@@ -409,6 +412,9 @@ public class RexImpTable {
defineMethod(DATE, "date", NullPolicy.STRICT);
defineMethod(DATETIME, "datetime", NullPolicy.STRICT);
defineMethod(TIME, "time", NullPolicy.STRICT);
+ defineReflective(TO_CHAR, BuiltInMethod.TO_CHAR.method);
+ defineReflective(TO_DATE, BuiltInMethod.TO_DATE.method);
+ defineReflective(TO_TIMESTAMP, BuiltInMethod.TO_TIMESTAMP.method);
map.put(IS_NULL, new IsNullImplementor());
map.put(IS_NOT_NULL, new IsNotNullImplementor());
diff --git
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/sql/fun/IgniteStdSqlOperatorTable.java
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/sql/fun/IgniteStdSqlOperatorTable.java
index e69db3e3173..e496c4ff5f3 100644
---
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/sql/fun/IgniteStdSqlOperatorTable.java
+++
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/sql/fun/IgniteStdSqlOperatorTable.java
@@ -217,6 +217,9 @@ public class IgniteStdSqlOperatorTable extends
ReflectiveSqlOperatorTable {
register(SqlLibraryOperators.DATE); // String to date.
register(SqlLibraryOperators.DATETIME); // String to datetime.
register(SqlLibraryOperators.TIME); // String to time.
+ register(SqlLibraryOperators.TO_CHAR); // Format date.
+ register(SqlLibraryOperators.TO_DATE); // Parse date.
+ register(SqlLibraryOperators.TO_TIMESTAMP); // Parse timestamp.
// POSIX REGEX.
register(SqlStdOperatorTable.POSIX_REGEX_CASE_INSENSITIVE);
diff --git
a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/StdSqlOperatorsTest.java
b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/StdSqlOperatorsTest.java
index 98da886d395..9df6eba2db6 100644
---
a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/StdSqlOperatorsTest.java
+++
b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/StdSqlOperatorsTest.java
@@ -259,6 +259,9 @@ public class StdSqlOperatorsTest extends
AbstractBasicIntegrationTest {
assertExpression("DATE('2021-01-01')").returns(Date.valueOf("2021-01-01")).check();
assertExpression("TIME(1, 10,
30)").returns(Time.valueOf("01:10:30")).check();
assertExpression("DATETIME(2021, 1, 1, 1, 10,
30)").returns(Timestamp.valueOf("2021-01-01 01:10:30")).check();
+ assertExpression("TO_CHAR(DATE '2021-01-01',
'YYMMDD')").returns("210101").check();
+ assertExpression("TO_DATE('210101',
'YYMMDD')").returns(Date.valueOf("2021-01-01")).check();
+ assertExpression("TO_TIMESTAMP('210101-01-10-30',
'YYMMDD-HH24-MI-SS')").returns(Timestamp.valueOf("2021-01-01
01:10:30")).check();
}
/** */
