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 2023/03/20 20:09:17 UTC
[calcite] branch main updated: [CALCITE-5476] Add DATETIME_TRUNC function (enabled for BigQuery)
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 0c5e7c8510 [CALCITE-5476] Add DATETIME_TRUNC function (enabled for BigQuery)
0c5e7c8510 is described below
commit 0c5e7c851063e40113d2500d3a1d5cae370ce564
Author: Oliver Lee <ol...@google.com>
AuthorDate: Tue Feb 28 00:28:17 2023 +0000
[CALCITE-5476] Add DATETIME_TRUNC function (enabled for BigQuery)
Close apache/calcite#3086
---
babel/src/test/resources/sql/big-query.iq | 16 ++++----
core/src/main/codegen/default_config.fmpp | 1 +
core/src/main/codegen/templates/Parser.jj | 26 ++++++++++++-
.../calcite/adapter/enumerable/RexImpTable.java | 2 +
.../calcite/sql/fun/SqlLibraryOperators.java | 43 ++++++++++++++++++----
site/_docs/reference.md | 2 +
.../org/apache/calcite/test/SqlOperatorTest.java | 38 +++++++++++++++++++
7 files changed, 113 insertions(+), 15 deletions(-)
diff --git a/babel/src/test/resources/sql/big-query.iq b/babel/src/test/resources/sql/big-query.iq
index 998b7259f6..9b6a0fc51b 100755
--- a/babel/src/test/resources/sql/big-query.iq
+++ b/babel/src/test/resources/sql/big-query.iq
@@ -2171,17 +2171,19 @@ SELECT
#
# Returns DATETIME
-!if (false) {
+
SELECT
DATETIME "2008-12-25 15:30:00" as original,
DATETIME_TRUNC(DATETIME "2008-12-25 15:30:00", DAY) as truncated;
-+----------------------------+------------------------+
-| original | truncated |
-+----------------------------+------------------------+
-| 2008-12-25T15:30:00 | 2008-12-25T00:00:00 |
-+----------------------------+------------------------+
++---------------------+---------------------+
+| original | truncated |
++---------------------+---------------------+
+| 2008-12-25 15:30:00 | 2008-12-25 00:00:00 |
++---------------------+---------------------+
+(1 row)
+
!ok
-!}
+
# In the following example, the original DATETIME falls on a
# Sunday. Because the part is WEEK(MONDAY), DATE_TRUNC returns the
diff --git a/core/src/main/codegen/default_config.fmpp b/core/src/main/codegen/default_config.fmpp
index 950dd32286..3e8e792e12 100644
--- a/core/src/main/codegen/default_config.fmpp
+++ b/core/src/main/codegen/default_config.fmpp
@@ -88,6 +88,7 @@ parser: {
"DATETIME_DIFF"
"DATETIME_INTERVAL_CODE"
"DATETIME_INTERVAL_PRECISION"
+ "DATETIME_TRUNC"
"DAYOFWEEK"
"DAYOFYEAR"
"DAYS"
diff --git a/core/src/main/codegen/templates/Parser.jj b/core/src/main/codegen/templates/Parser.jj
index 505aa64343..a30408ff84 100644
--- a/core/src/main/codegen/templates/Parser.jj
+++ b/core/src/main/codegen/templates/Parser.jj
@@ -6156,6 +6156,8 @@ SqlNode BuiltinFunctionCall() :
node = DateDiffFunctionCall() { return node; }
|
node = DateTruncFunctionCall() { return node; }
+ |
+ node = DatetimeTruncFunctionCall() { return node; }
|
node = TimestampAddFunctionCall() { return node; }
|
@@ -6856,6 +6858,27 @@ SqlCall TimeDiffFunctionCall() :
}
}
+/**
+ * Parses a call to DATETIME_TRUNC.
+ */
+SqlNode DatetimeTruncFunctionCall() :
+{
+ final List<SqlNode> args = new ArrayList<SqlNode>();
+ final Span s;
+ final SqlIntervalQualifier unit;
+ final SqlNode literal;
+}
+{
+ <DATETIME_TRUNC> { s = span(); }
+ <LPAREN>
+ AddExpression(args, ExprContext.ACCEPT_SUB_QUERY)
+ <COMMA>
+ unit = TimeUnitOrName() { args.add(unit); }
+ <RPAREN> {
+ return SqlLibraryOperators.DATETIME_TRUNC.createCall(s.end(this), args);
+ }
+}
+
/**
* Parses a call to TIME_TRUNC.
*/
@@ -7779,12 +7802,13 @@ SqlPostfixOperator PostfixRowOperator() :
| < DATA: "DATA" >
| < DATABASE: "DATABASE" >
| < DATE: "DATE" >
+| < DATE_DIFF: "DATE_DIFF" >
| < DATE_TRUNC: "DATE_TRUNC" >
| < DATETIME: "DATETIME" >
| < DATETIME_DIFF: "DATETIME_DIFF" >
| < DATETIME_INTERVAL_CODE: "DATETIME_INTERVAL_CODE" >
| < DATETIME_INTERVAL_PRECISION: "DATETIME_INTERVAL_PRECISION" >
-| < DATE_DIFF: "DATE_DIFF" >
+| < DATETIME_TRUNC: "DATETIME_TRUNC" >
| < DAY: "DAY" >
| < DAYOFWEEK: "DAYOFWEEK" >
| < DAYOFYEAR: "DAYOFYEAR" >
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 723a18ae70..503bee6e19 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
@@ -125,6 +125,7 @@ import static org.apache.calcite.sql.fun.SqlLibraryOperators.COSH;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.DATE;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.DATEADD;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.DATETIME;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.DATETIME_TRUNC;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.DATE_FROM_UNIX_DATE;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.DATE_TRUNC;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.DAYNAME;
@@ -537,6 +538,7 @@ public class RexImpTable {
map.put(DATE_TRUNC, map.get(FLOOR));
map.put(TIMESTAMP_TRUNC, map.get(FLOOR));
map.put(TIME_TRUNC, map.get(FLOOR));
+ map.put(DATETIME_TRUNC, map.get(FLOOR));
map.put(LAST_DAY,
new LastDayImplementor("lastDay", BuiltInMethod.LAST_DAY));
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 620cb5b724..fe03c2b2d7 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
@@ -529,7 +529,8 @@ public abstract class SqlLibraryOperators {
/** The "DATETIME" function returns a Calcite
* {@code TIMESTAMP} (which BigQuery calls a {@code DATETIME}).
- * It has the following overloads:
+ *
+ * <p>It has the following overloads:
*
* <ul>
* <li>{@code DATETIME(year, month, day, hour, minute, second)}
@@ -838,7 +839,10 @@ public abstract class SqlLibraryOperators {
SqlFunctionCategory.STRING);
/** The "FORMAT_DATETIME(string, timestamp)" function (BigQuery);
- * Formats a timestamp object according to the specified string. */
+ * formats a timestamp object according to the specified string.
+ *
+ * <p>Note that the {@code TIMESTAMP} type of Calcite and Standard SQL
+ * is called {@code DATETIME} in BigQuery. */
@LibraryOperator(libraries = {BIG_QUERY})
public static final SqlFunction FORMAT_DATETIME =
SqlBasicFunction.create("FORMAT_DATETIME",
@@ -938,15 +942,23 @@ public abstract class SqlLibraryOperators {
OperandTypes.TIMESTAMP_INTERVAL)
.withFunctionType(SqlFunctionCategory.TIMEDATE);
- /** BigQuery's {@code DATETIME_SUB(timestamp, interval)} function
- * is a synonym for TIMESTAMP_SUB because in Calcite, DATETIME
- * is an alias for TIMESTAMP. */
+ /** The "DATETIME_SUB(timestamp, interval)" function (BigQuery).
+ *
+ * <p>Note that the {@code TIMESTAMP} type of Calcite and Standard SQL
+ * is called {@code DATETIME} in BigQuery.
+ *
+ * <p>A synonym for {@link #TIMESTAMP_SUB}, which supports both
+ * {@code TIMESTAMP} and {@code TIMESTAMP WITH LOCAL TIME ZONE} operands. */
@LibraryOperator(libraries = {BIG_QUERY})
public static final SqlFunction DATETIME_SUB =
TIMESTAMP_SUB.withName("DATETIME_SUB");
/** The "TIMESTAMP_TRUNC(timestamp, timeUnit[, timeZone])" function (BigQuery);
- * truncates a TIMESTAMP value to the beginning of a timeUnit. */
+ * truncates a {@code TIMESTAMP WITH LOCAL TIME ZONE} value to the beginning
+ * of a timeUnit.
+ *
+ * <p>Note that the {@code TIMESTAMP WITH LOCAL TIME ZONE} type of Calcite
+ * is called {@code TIMESTAMP} in BigQuery. */
@LibraryOperator(libraries = {BIG_QUERY})
public static final SqlFunction TIMESTAMP_TRUNC =
SqlBasicFunction.create("TIMESTAMP_TRUNC",
@@ -956,6 +968,20 @@ public abstract class SqlLibraryOperators {
OperandTypes.TIMESTAMP, OperandTypes.timestampInterval()),
SqlFunctionCategory.TIMEDATE);
+ /** The "DATETIME_TRUNC(timestamp, timeUnit)" function (BigQuery);
+ * truncates a TIMESTAMP value to the beginning of a timeUnit.
+ *
+ * <p>Note that the {@code TIMESTAMP} type of Calcite and Standard SQL
+ * is called {@code DATETIME} in BigQuery. */
+ @LibraryOperator(libraries = {BIG_QUERY})
+ public static final SqlFunction DATETIME_TRUNC =
+ SqlBasicFunction.create("DATETIME_TRUNC",
+ ReturnTypes.TIMESTAMP_NULLABLE,
+ OperandTypes.sequence(
+ "'DATETIME_TRUNC(<TIMESTAMP>, <DATETIME_INTERVAL>)'",
+ OperandTypes.TIMESTAMP, OperandTypes.timestampInterval()),
+ SqlFunctionCategory.TIMEDATE);
+
/** The "TIMESTAMP_SECONDS(bigint)" function; returns a TIMESTAMP value
* a given number of seconds after 1970-01-01 00:00:00. */
@LibraryOperator(libraries = {BIG_QUERY})
@@ -1010,7 +1036,10 @@ public abstract class SqlLibraryOperators {
public static final SqlFunction DATETIME_ADD =
TIMESTAMP_ADD2.withName("DATETIME_ADD");
- /** The "DATETIME_DIFF(timestamp, timestamp2, timeUnit)" function (BigQuery). */
+ /** The "DATETIME_DIFF(timestamp, timestamp2, timeUnit)" function (BigQuery).
+ *
+ * <p>Note that the {@code TIMESTAMP} type of Calcite and Standard SQL
+ * is called {@code DATETIME} in BigQuery. */
@LibraryOperator(libraries = {BIG_QUERY})
public static final SqlFunction DATETIME_DIFF =
new SqlTimestampDiffFunction("DATETIME_DIFF",
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 8c28999010..587bac802e 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -547,6 +547,7 @@ DATABASE,
DATETIME_DIFF,
DATETIME_INTERVAL_CODE,
DATETIME_INTERVAL_PRECISION,
+DATETIME_TRUNC,
DATE_DIFF,
DATE_TRUNC,
**DAY**,
@@ -2656,6 +2657,7 @@ BigQuery's type system uses confusingly different names for types and functions:
| b | DATETIME_ADD(timestamp, interval) | Returns the TIMESTAMP value that occurs *interval* after *timestamp*
| b | DATETIME_DIFF(timestamp, timestamp2, timeUnit) | Returns the whole number of *timeUnit* between *timestamp* and *timestamp2*
| b | DATETIME_SUB(timestamp, interval) | Returns the TIMESTAMP that occurs *interval* before *timestamp*
+| b | DATETIME_TRUNC(timestamp, timeUnit) | Truncates *timestamp* to the granularity of *timeUnit*, rounding to the beginning of the unit
| b | DATE_FROM_UNIX_DATE(integer) | Returns the DATE that is *integer* days after 1970-01-01
| p | DATE_PART(timeUnit, datetime) | Equivalent to `EXTRACT(timeUnit FROM datetime)`
| b | DATE_ADD(date, interval) | Returns the DATE value that occurs *interval* after *date*
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 ed26bd31e3..98f7830048 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -8700,6 +8700,44 @@ public class SqlOperatorTest {
"2015-01-01 00:00:00", "TIMESTAMP(0) NOT NULL");
}
+ @Test void testDatetimeTrunc() {
+ SqlOperatorFixture nonBigQuery = fixture()
+ .setFor(SqlLibraryOperators.DATETIME_TRUNC);
+ nonBigQuery.checkFails("^datetime_trunc(timestamp '2012-05-02 15:30:00', hour)^",
+ "No match found for function signature "
+ + "DATETIME_TRUNC\\(<TIMESTAMP>, <INTERVAL_DAY_TIME>\\)",
+ false);
+
+ final SqlOperatorFixture f = fixture()
+ .withLibrary(SqlLibrary.BIG_QUERY)
+ .setFor(SqlLibraryOperators.DATETIME_TRUNC);
+ f.checkFails("^datetime_trunc(100, hour)^",
+ "Cannot apply 'DATETIME_TRUNC' to arguments of type "
+ + "'DATETIME_TRUNC\\(<INTEGER>, <INTERVAL HOUR>\\)'\\. "
+ + "Supported form\\(s\\): 'DATETIME_TRUNC\\(<TIMESTAMP>, <DATETIME_INTERVAL>\\)'",
+ false);
+ f.checkFails("^datetime_trunc(100, foo)^",
+ "Cannot apply 'DATETIME_TRUNC' to arguments of type "
+ + "'DATETIME_TRUNC\\(<INTEGER>, <INTERVAL `FOO`>\\)'\\. "
+ + "Supported form\\(s\\): 'DATETIME_TRUNC\\(<TIMESTAMP>, <DATETIME_INTERVAL>\\)'",
+ false);
+
+ f.checkScalar("datetime_trunc(timestamp '2015-02-19 12:34:56.78', second)",
+ "2015-02-19 12:34:56", "TIMESTAMP(0) NOT NULL");
+ f.checkScalar("datetime_trunc(timestamp '2015-02-19 12:34:56', minute)",
+ "2015-02-19 12:34:00", "TIMESTAMP(0) NOT NULL");
+ f.checkScalar("datetime_trunc(timestamp '2015-02-19 12:34:56', hour)",
+ "2015-02-19 12:00:00", "TIMESTAMP(0) NOT NULL");
+ f.checkScalar("datetime_trunc(timestamp '2015-02-19 12:34:56', day)",
+ "2015-02-19 00:00:00", "TIMESTAMP(0) NOT NULL");
+ f.checkScalar("datetime_trunc(timestamp '2015-02-19 12:34:56', week)",
+ "2015-02-15 00:00:00", "TIMESTAMP(0) NOT NULL");
+ f.checkScalar("datetime_trunc(timestamp '2015-02-19 12:34:56', month)",
+ "2015-02-01 00:00:00", "TIMESTAMP(0) NOT NULL");
+ f.checkScalar("datetime_trunc(timestamp '2015-02-19 12:34:56', year)",
+ "2015-01-01 00:00:00", "TIMESTAMP(0) NOT NULL");
+ }
+
@Test void testDateTrunc() {
final SqlOperatorFixture f = fixture()
.withLibrary(SqlLibrary.BIG_QUERY)