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
commit ba975ba0612768c24677a74fbb9bf7a2922bce36 Author: Oliver Lee <[email protected]> AuthorDate: Tue Feb 28 00:28:17 2023 +0000 [CALCITE-5476] Add DATETIME_TRUNC function (enabled in BigQuery library) 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)
