This is an automated email from the ASF dual-hosted git repository.
jhyde pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new 4b37ed377a [CALCITE-5761] Allow DECADE, CENTURY, and MILLENNIUM time
units in DATE_TRUNC, TIMESTAMP_TRUNC, DATETIME_TRUNC functions
4b37ed377a is described below
commit 4b37ed377a08953822ccff105c8ba53cd57b94fe
Author: Mihai Budiu <[email protected]>
AuthorDate: Wed Jun 7 16:28:09 2023 -0700
[CALCITE-5761] Allow DECADE, CENTURY, and MILLENNIUM time units in
DATE_TRUNC, TIMESTAMP_TRUNC, DATETIME_TRUNC functions
Close apache/calcite#3253
Signed-off-by: Mihai Budiu <[email protected]>
---
.../calcite/adapter/enumerable/RexImpTable.java | 10 +++++++-
.../main/java/org/apache/calcite/sql/SqlKind.java | 3 +++
.../calcite/sql/fun/SqlLibraryOperators.java | 3 ++-
.../apache/calcite/sql/parser/SqlParserTest.java | 6 ++---
.../org/apache/calcite/test/SqlOperatorTest.java | 30 ++++++++++++++++++++++
5 files changed, 47 insertions(+), 5 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
index f9ca53ecbe..ab4eb88d5a 100644
--- a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
+++ b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
@@ -2394,16 +2394,24 @@ public class RexImpTable {
case MONTH:
case WEEK:
case DAY:
+ case DECADE:
+ case CENTURY:
+ case MILLENNIUM:
final Expression dayOperand0 =
preFloor ? call(operand0, type, TimeUnit.DAY) : operand0;
return Expressions.call(floorMethod,
translator.getLiteral(operand1), dayOperand0);
default:
+ if (call.op.getKind() == SqlKind.DATE_TRUNC) {
+ throw new IllegalArgumentException("Time unit " + timeUnitRange
+ + " not supported for " + call.op.getName());
+ }
return call(operand0, type, timeUnitRange.startUnit);
}
default:
- throw new AssertionError();
+ throw new AssertionError(call.op.getName()
+ + " only supported with 1 or 2 arguments");
}
}
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlKind.java
b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
index 9a3a4c806f..6f3f3a747c 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlKind.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
@@ -433,6 +433,9 @@ public enum SqlKind {
/** {@code DATE_DIFF} function (BigQuery Semantics). */
DATE_ADD,
+ /** {@code DATE_TRUNC} function (BigQuery). */
+ DATE_TRUNC,
+
/** {@code DATE_SUB} function (BigQuery). */
DATE_SUB,
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 7a3e4f193e..18de7a7c1e 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
@@ -1205,7 +1205,8 @@ public abstract class SqlLibraryOperators {
OperandTypes.sequence("'DATE_TRUNC(<DATE>, <DATETIME_INTERVAL>)'",
OperandTypes.DATE_OR_TIMESTAMP, OperandTypes.dateInterval()),
SqlFunctionCategory.TIMEDATE)
- .withOperandHandler(OperandHandlers.OPERAND_1_MIGHT_BE_TIME_FRAME);
+ .withOperandHandler(OperandHandlers.OPERAND_1_MIGHT_BE_TIME_FRAME)
+ .withKind(SqlKind.DATE_TRUNC);
/** The "TIME_SUB(time, interval)" function (BigQuery);
* subtracts an interval from a time, independent of any time zone.
diff --git
a/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java
b/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java
index a4db920839..d78fab4324 100644
--- a/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java
+++ b/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java
@@ -6670,9 +6670,9 @@ public class SqlParserTest {
expr("extract(microsecond from d)").ok("EXTRACT(MICROSECOND FROM `D`)");
// As for FLOOR, so for DATE_TRUNC.
- expr("date_trunc(d , year)").ok("DATE_TRUNC(`D`, YEAR)");
- expr("date_trunc(d , y)").ok("DATE_TRUNC(`D`, `Y`)");
- expr("date_trunc(d , week(tuesday))").ok("DATE_TRUNC(`D`,
`WEEK_TUESDAY`)");
+ expr("date_trunc(d , year)").ok("(DATE_TRUNC(`D`, YEAR))");
+ expr("date_trunc(d , y)").ok("(DATE_TRUNC(`D`, `Y`))");
+ expr("date_trunc(d , week(tuesday))").ok("(DATE_TRUNC(`D`,
`WEEK_TUESDAY`))");
}
@Test void testGeometry() {
diff --git a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
index b9bd7d8f78..5fd3078052 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -9696,6 +9696,16 @@ public class SqlOperatorTest {
// verify return type for dates
f.checkScalar("timestamp_trunc(date '2008-12-25', month)",
"2008-12-01 00:00:00", "TIMESTAMP(0) NOT NULL");
+ f.checkScalar("timestamp_trunc(timestamp '2015-02-19 12:34:56', decade)",
+ "2010-01-01 00:00:00", "TIMESTAMP(0) NOT NULL");
+ // It may be surprising that this returns 2001 (and not 2000),
+ // but the definition requires the "first day of the century".
+ // See DateTimeUtils.julianDateFloor in Calcite Avatica.
+ f.checkScalar("timestamp_trunc(timestamp '2015-02-19 12:34:56', century)",
+ "2001-01-01 00:00:00", "TIMESTAMP(0) NOT NULL");
+ // The comment above for century applies to millennium too.
+ f.checkScalar("timestamp_trunc(timestamp '2015-02-19 12:34:56',
millennium)",
+ "2001-01-01 00:00:00", "TIMESTAMP(0) NOT NULL");
f.checkFails("^timestamp_trunc(time '15:30:00', hour)^",
"Cannot apply 'TIMESTAMP_TRUNC' to arguments of type "
+ "'TIMESTAMP_TRUNC\\(<TIME\\(0\\)>, <INTERVAL HOUR>\\)'\\. "
@@ -9742,6 +9752,16 @@ public class SqlOperatorTest {
// verify return type for dates
f.checkScalar("datetime_trunc(date '2008-12-25', month)",
"2008-12-01 00:00:00", "TIMESTAMP(0) NOT NULL");
+ f.checkScalar("datetime_trunc(date '2015-02-19', decade)",
+ "2010-01-01 00:00:00", "TIMESTAMP(0) NOT NULL");
+ // It may be surprising that this returns 2001 (and not 2000),
+ // but the definition requires the "first day of the century".
+ // See DateTimeUtils.julianDateFloor in Calcite Avatica.
+ f.checkScalar("datetime_trunc(date '2015-02-19', century)",
+ "2001-01-01 00:00:00", "TIMESTAMP(0) NOT NULL");
+ // The comment above for century applies to millennium too.
+ f.checkScalar("datetime_trunc(date '2015-02-19', millennium)",
+ "2001-01-01 00:00:00", "TIMESTAMP(0) NOT NULL");
f.checkFails("^datetime_trunc(time '15:30:00', hour)^",
"Cannot apply 'DATETIME_TRUNC' to arguments of type "
+ "'DATETIME_TRUNC\\(<TIME\\(0\\)>, <INTERVAL HOUR>\\)'\\. "
@@ -9791,6 +9811,16 @@ public class SqlOperatorTest {
+ "'DATE_TRUNC\\(<TIME\\(0\\)>, <INTERVAL HOUR>\\)'\\. "
+ "Supported form\\(s\\): 'DATE_TRUNC\\(<DATE>,
<DATETIME_INTERVAL>\\)'",
false);
+ f.checkScalar("date_trunc(date '2015-02-19', decade)",
+ "2010-01-01", "DATE NOT NULL");
+ // It may be surprising that this returns 2001 (and not 2000),
+ // but the definition requires the "first day of the century".
+ // See DateTimeUtils.julianDateFloor in Calcite Avatica.
+ f.checkScalar("date_trunc(date '2015-02-19', century)",
+ "2001-01-01", "DATE NOT NULL");
+ // The comment above for century applies to millennium too.
+ f.checkScalar("date_trunc(date '2015-02-19', millennium)",
+ "2001-01-01", "DATE NOT NULL");
}
@Test void testFormatTime() {
