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 a505b25eacc473c6ec0ef8abd40c1ccae86297b6 Author: TJ Banghart <[email protected]> AuthorDate: Mon Sep 19 21:15:38 2022 +0000 [CALCITE-5269] Implement BigQuery TIME_TRUNC and TIMESTAMP_TRUNC functions Make negative parser tests pass on Windows. (Previously, we assumed that the error message would have Linux line endings.) Close apache/calcite#2913 --- babel/src/main/codegen/config.fmpp | 2 + babel/src/test/resources/sql/big-query.iq | 57 ++++++++++------ core/src/main/codegen/default_config.fmpp | 2 + core/src/main/codegen/templates/Parser.jj | 77 +++++++++++++++++++++- .../calcite/adapter/enumerable/RexImpTable.java | 13 ++++ .../calcite/sql/fun/SqlLibraryOperators.java | 25 +++++++ .../org/apache/calcite/sql/type/OperandTypes.java | 6 ++ .../calcite/rel/rel2sql/RelToSqlConverterTest.java | 15 +++++ site/_docs/reference.md | 4 ++ .../apache/calcite/sql/parser/SqlParserTest.java | 18 +++++ .../apache/calcite/sql/test/AbstractSqlTester.java | 2 +- .../org/apache/calcite/test/SqlOperatorTest.java | 68 +++++++++++++++++++ 12 files changed, 266 insertions(+), 23 deletions(-) diff --git a/babel/src/main/codegen/config.fmpp b/babel/src/main/codegen/config.fmpp index 3562e254dc..c4168665b7 100644 --- a/babel/src/main/codegen/config.fmpp +++ b/babel/src/main/codegen/config.fmpp @@ -463,7 +463,9 @@ data: { "TEMPORARY" # "THEN" # "TIME" + "TIME_TRUNC" # "TIMESTAMP" + "TIMESTAMP_TRUNC" "TIMEZONE_HOUR" "TIMEZONE_MINUTE" "TINYINT" diff --git a/babel/src/test/resources/sql/big-query.iq b/babel/src/test/resources/sql/big-query.iq index b6a23a0f3c..61c07698ae 100755 --- a/babel/src/test/resources/sql/big-query.iq +++ b/babel/src/test/resources/sql/big-query.iq @@ -766,15 +766,25 @@ SELECT TIMESTAMP(DATE "2008-12-25") AS timestamp_date; # Display of results may differ, depending upon the environment and # time zone where this query was executed. -!if (false) { + +# Note that BigQuery prints "2008-12-25 15:30:00 UTC" as the result, +# whereas Calcite's result does not include "UTC". This is because +# BigQuery's TIMESTAMP type is an instant (the equivalent of Calcite's +# TIMESTAMP WITH LOCAL TIME ZONE type). It would have been too confusing +# for a function called 'TIMESTAMP_SECONDS' to return a TIMESTAMP WITH +# LOCAL TIME ZONE, so this function returns a Calcite TIMESTAMP which, +# as a local time does not include UTC or any time zone. + SELECT TIMESTAMP_SECONDS(1230219000) AS timestamp_value; -+-------------------------+ -| timestamp_value | -+-------------------------+ -| 2008-12-25 15:30:00 UTC | -+-------------------------+ ++---------------------+ +| timestamp_value | ++---------------------+ +| 2008-12-25 15:30:00 | ++---------------------+ +(1 row) + !ok -!} + ##################################################################### # TIMESTAMP_MILLIS @@ -786,15 +796,20 @@ SELECT TIMESTAMP_SECONDS(1230219000) AS timestamp_value; # Display of results may differ, depending upon the environment and # time zone where this query was executed. -!if (false) { + +# BigQuery's result ends in "UTC" but Calcite's does not, for the reasons +# explained in TIMESTAMP_SECONDS. + SELECT TIMESTAMP_MILLIS(1230219000000) AS timestamp_value; -+-------------------------+ -| timestamp_value | -+-------------------------+ -| 2008-12-25 15:30:00 UTC | -+-------------------------+ ++---------------------+ +| timestamp_value | ++---------------------+ +| 2008-12-25 15:30:00 | ++---------------------+ +(1 row) + !ok -!} + ##################################################################### # TIMESTAMP_MICROS @@ -1812,17 +1827,17 @@ SELECT # # Returns TIME -!if (false) { SELECT TIME "15:30:00" as original, TIME_TRUNC(TIME "15:30:00", HOUR) as truncated; -+----------------------------+------------------------+ -| original | truncated | -+----------------------------+------------------------+ -| 15:30:00 | 15:00:00 | -+----------------------------+------------------------+ ++----------+-----------+ +| original | truncated | ++----------+-----------+ +| 15:30:00 | 15:00:00 | ++----------+-----------+ +(1 row) + !ok -!} ##################################################################### # TIMESTAMP_TRUNC diff --git a/core/src/main/codegen/default_config.fmpp b/core/src/main/codegen/default_config.fmpp index 8e9cdc0041..22ca9289ec 100644 --- a/core/src/main/codegen/default_config.fmpp +++ b/core/src/main/codegen/default_config.fmpp @@ -314,8 +314,10 @@ parser: { "TABLE_NAME" "TEMPORARY" "TIES" + "TIME_TRUNC" "TIMESTAMPADD" "TIMESTAMPDIFF" + "TIMESTAMP_TRUNC" "TOP_LEVEL_COUNT" "TRANSACTION" "TRANSACTIONS_ACTIVE" diff --git a/core/src/main/codegen/templates/Parser.jj b/core/src/main/codegen/templates/Parser.jj index 9ad6582315..7f26f806f3 100644 --- a/core/src/main/codegen/templates/Parser.jj +++ b/core/src/main/codegen/templates/Parser.jj @@ -4986,6 +4986,19 @@ TimeUnit TimeUnitForExtract() : unit = TimeUnit() { return unit; } } +/** + * Parses time unit for the TIME_TRUNC function. + * This is a subset of time units appropriate for TIME data type. + */ +TimeUnit TimeUnitForTimeTrunc() : +{} +{ + <MILLISECOND> { return TimeUnit.MILLISECOND; } +| <SECOND> { return TimeUnit.SECOND; } +| <MINUTE> { return TimeUnit.MINUTE; } +| <HOUR> { return TimeUnit.HOUR; } +} + /** * Parses a simple identifier as a TimeUnit. */ @@ -6023,6 +6036,10 @@ SqlNode BuiltinFunctionCall() : | node = TimestampDiffFunctionCall() { return node; } | + node = TimestampTruncFunctionCall() { return node; } + | + node = TimeTruncFunctionCall() { return node; } + | <#list (parser.builtinFunctionCallMethods!default.parser.builtinFunctionCallMethods) as method> node = ${method} { return node; } | @@ -6571,11 +6588,55 @@ SqlCall TimestampDiffFunctionCall() : } } +/** + * Parses a call to TIMESTAMP_TRUNC. + */ +SqlCall TimestampTruncFunctionCall() : +{ + final List<SqlNode> args = new ArrayList<SqlNode>(); + final Span s; + final TimeUnit unit; +} +{ + <TIMESTAMP_TRUNC> { s = span(); } + <LPAREN> + AddExpression(args, ExprContext.ACCEPT_SUB_QUERY) + <COMMA> + unit = TimeUnit() { + args.add(new SqlIntervalQualifier(unit, null, getPos())); + } + <RPAREN> { + return SqlLibraryOperators.TIMESTAMP_TRUNC.createCall(s.end(this), args); + } +} + +/** + * Parses a call to TIME_TRUNC. + */ +SqlCall TimeTruncFunctionCall() : +{ + final List<SqlNode> args = new ArrayList<SqlNode>(); + final Span s; + final TimeUnit unit; +} +{ + <TIME_TRUNC> { s = span(); } + <LPAREN> + AddExpression(args, ExprContext.ACCEPT_SUB_QUERY) + <COMMA> + unit = TimeUnitForTimeTrunc() { + args.add(new SqlIntervalQualifier(unit, null, getPos())); + } + <RPAREN> { + return SqlLibraryOperators.TIME_TRUNC.createCall(s.end(this), args); + } +} + /** * Parses a call to a grouping function inside the GROUP BY clause, * for example {@code TUMBLE(rowtime, INTERVAL '1' MINUTE)}. */ -SqlCall GroupByWindowingCall(): +SqlCall GroupByWindowingCall() : { final Span s; final List<SqlNode> args; @@ -7064,6 +7125,18 @@ SqlNode JdbcFunctionCall() : name = call.getOperator().getName(); args = new SqlNodeList(call.getOperandList(), getPos()); } + | + LOOKAHEAD(1) + call = TimestampTruncFunctionCall() { + name = call.getOperator().getName(); + args = new SqlNodeList(call.getOperandList(), getPos()); + } + | + LOOKAHEAD(1) + call = TimeTruncFunctionCall() { + name = call.getOperator().getName(); + args = new SqlNodeList(call.getOperandList(), getPos()); + } | LOOKAHEAD(3) call = TimestampDiffFunctionCall() { @@ -7905,9 +7978,11 @@ SqlPostfixOperator PostfixRowOperator() : | < THEN: "THEN" > | < TIES: "TIES" > | < TIME: "TIME" > +| < TIME_TRUNC: "TIME_TRUNC" > | < TIMESTAMP: "TIMESTAMP" > | < TIMESTAMPADD: "TIMESTAMPADD" > | < TIMESTAMPDIFF: "TIMESTAMPDIFF" > +| < TIMESTAMP_TRUNC: "TIMESTAMP_TRUNC" > | < TIMEZONE_HOUR: "TIMEZONE_HOUR" > | < TIMEZONE_MINUTE: "TIMEZONE_MINUTE" > | < TINYINT: "TINYINT" > 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 1864b1e459..f081d7dee7 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 @@ -156,6 +156,8 @@ import static org.apache.calcite.sql.fun.SqlLibraryOperators.TANH; 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.TIMESTAMP_TRUNC; +import static org.apache.calcite.sql.fun.SqlLibraryOperators.TIME_TRUNC; import static org.apache.calcite.sql.fun.SqlLibraryOperators.TO_BASE64; import static org.apache.calcite.sql.fun.SqlLibraryOperators.TRANSLATE3; import static org.apache.calcite.sql.fun.SqlLibraryOperators.UNIX_DATE; @@ -459,6 +461,17 @@ public class RexImpTable { BuiltInMethod.UNIX_TIMESTAMP_CEIL.method, BuiltInMethod.UNIX_DATE_CEIL.method)); + // TIMESTAMP_TRUNC and TIME_TRUNC methods are syntactic sugar for standard datetime FLOOR + map.put(TIMESTAMP_TRUNC, + new FloorImplementor(BuiltInMethod.FLOOR.method.getName(), + BuiltInMethod.UNIX_TIMESTAMP_FLOOR.method, + BuiltInMethod.UNIX_DATE_FLOOR.method)); + map.put(TIME_TRUNC, + new FloorImplementor(BuiltInMethod.FLOOR.method.getName(), + BuiltInMethod.UNIX_TIMESTAMP_FLOOR.method, + BuiltInMethod.UNIX_DATE_FLOOR.method)); + + defineMethod(LAST_DAY, "lastDay", NullPolicy.STRICT); map.put(DAYNAME, new PeriodNameImplementor("dayName", 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 794270682c..2691ad4935 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 @@ -602,6 +602,31 @@ public abstract class SqlLibraryOperators { OperandTypes.STRING_STRING, SqlFunctionCategory.TIMEDATE); + /** The "TIME_TRUNC(time_expression, time_part)" function (BigQuery); + * truncates a TIME value to the granularity of time_part. The TIME value is + * always rounded to the beginning of time_part. */ + @LibraryOperator(libraries = {BIG_QUERY}) + public static final SqlFunction TIME_TRUNC = + new SqlFunction("TIME_TRUNC", + SqlKind.OTHER_FUNCTION, + ReturnTypes.TIME_NULLABLE, + null, + OperandTypes.TIME_INTERVAL, + SqlFunctionCategory.TIMEDATE); + + /** The "TIMESTAMP_TRUNC(timestamp_expression, date_time_part[, time_zone])" + * function (BigQuery); truncates a TIMESTAMP value to the granularity of + * date_time_part. The TIMESTAMP value is always rounded to the beginning of + * date_time_part. */ + @LibraryOperator(libraries = {BIG_QUERY}) + public static final SqlFunction TIMESTAMP_TRUNC = + new SqlFunction("TIMESTAMP_TRUNC", + SqlKind.OTHER_FUNCTION, + ReturnTypes.TIMESTAMP_NULLABLE, + null, + OperandTypes.TIMESTAMP_INTERVAL, + 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}) 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 67db9053e7..280bb14afb 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 @@ -551,6 +551,12 @@ public abstract class OperandTypes { public static final SqlSingleOperandTypeChecker INTERVAL_NUMERIC = family(SqlTypeFamily.DATETIME_INTERVAL, SqlTypeFamily.NUMERIC); + public static final SqlSingleOperandTypeChecker TIME_INTERVAL = + family(SqlTypeFamily.TIME, SqlTypeFamily.DATETIME_INTERVAL); + + public static final SqlSingleOperandTypeChecker TIMESTAMP_INTERVAL = + family(SqlTypeFamily.TIMESTAMP, SqlTypeFamily.DATETIME_INTERVAL); + public static final SqlSingleOperandTypeChecker DATETIME_INTERVAL = family(SqlTypeFamily.DATETIME, SqlTypeFamily.DATETIME_INTERVAL); diff --git a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java index a77987d52f..0db4ea2466 100644 --- a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java +++ b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java @@ -1738,6 +1738,21 @@ class RelToSqlConverterTest { sql(query).withBigQuery().ok(expected); } + @Test void testBigQueryTimeTruncFunctions() { + String timestampTrunc = "select timestamp_trunc(timestamp '2012-02-03 15:30:00', month)\n" + + "from \"foodmart\".\"product\"\n"; + final String expectedTimestampTrunc = + "SELECT TIMESTAMP_TRUNC(TIMESTAMP '2012-02-03 15:30:00', MONTH)\n" + + "FROM \"foodmart\".\"product\""; + sql(timestampTrunc).withLibrary(SqlLibrary.BIG_QUERY).ok(expectedTimestampTrunc); + + String timeTrunc = "select time_trunc(time '15:30:00', minute)\n" + + "from \"foodmart\".\"product\"\n"; + final String expectedTimeTrunc = "SELECT TIME_TRUNC(TIME '15:30:00', MINUTE)\n" + + "FROM \"foodmart\".\"product\""; + sql(timeTrunc).withLibrary(SqlLibrary.BIG_QUERY).ok(expectedTimeTrunc); + } + /** Test case for * <a href="https://issues.apache.org/jira/browse/CALCITE-3220";>[CALCITE-3220] * HiveSqlDialect should transform the SQL-standard TRIM function to TRIM, diff --git a/site/_docs/reference.md b/site/_docs/reference.md index e49c5fa6dd..98a9bd7c0e 100644 --- a/site/_docs/reference.md +++ b/site/_docs/reference.md @@ -1006,8 +1006,10 @@ TIES, **TIMESTAMP**, TIMESTAMPADD, TIMESTAMPDIFF, +TIMESTAMP_TRUNC, **TIMEZONE_HOUR**, **TIMEZONE_MINUTE**, +TIME_TRUNC, **TINYINT**, **TO**, TOP_LEVEL_COUNT, @@ -2627,6 +2629,8 @@ semantics. | b | TIMESTAMP_MICROS(integer) | Returns the TIMESTAMP that is *integer* microseconds after 1970-01-01 00:00:00 | b | TIMESTAMP_MILLIS(integer) | Returns the TIMESTAMP that is *integer* milliseconds after 1970-01-01 00:00:00 | b | TIMESTAMP_SECONDS(integer) | Returns the TIMESTAMP that is *integer* seconds after 1970-01-01 00:00:00 +| b | TIMESTAMP_TRUNC(timestamp, timeUnit) | Truncates a *timestamp* value to the granularity of *timeUnit*. The *timestamp* value is always rounded to the beginning of the *timeUnit*. +| b | TIME_TRUNC(time, timeUnit) | Truncates a *time* value to the granularity of *timeUnit*. The *time* value is always rounded to the beginning of timeUnit, which can be one of the following: MILLISECOND, SECOND, MINUTE, HOUR. | 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 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 e692dfaee2..198518f0db 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 @@ -8150,6 +8150,24 @@ public class SqlParserTest { sql(sql).ok(expected); } + @Test void testTimeTrunc() { + final String sql = "select time_trunc(TIME '15:30:00', hour) from t"; + final String expected = "SELECT TIME_TRUNC(TIME '15:30:00', HOUR)\n" + + "FROM `T`"; + sql(sql).ok(expected); + + // should fail for time unit not appropriate for TIME type. + final String weekSql = "select time_trunc(time '15:30:00', ^week^) from t"; + sql(weekSql).fails("(?s).*Was expecting one of.*"); + } + + @Test void testTimestampTrunc() { + final String sql = "select timestamp_trunc(timestamp '2008-12-25 15:30:00', week) from t"; + final String expected = "SELECT TIMESTAMP_TRUNC(TIMESTAMP '2008-12-25 15:30:00', WEEK)\n" + + "FROM `T`"; + sql(sql).ok(expected); + } + @Test void testUnnest() { sql("select*from unnest(x)") .ok("SELECT *\n" diff --git a/testkit/src/main/java/org/apache/calcite/sql/test/AbstractSqlTester.java b/testkit/src/main/java/org/apache/calcite/sql/test/AbstractSqlTester.java index fc50c9c6db..b2c474a96a 100644 --- a/testkit/src/main/java/org/apache/calcite/sql/test/AbstractSqlTester.java +++ b/testkit/src/main/java/org/apache/calcite/sql/test/AbstractSqlTester.java @@ -119,7 +119,7 @@ public abstract class AbstractSqlTester implements SqlTester, AutoCloseable { if (expectedMsgPattern == null) { throw new RuntimeException("Error while parsing query:" + sap, spe); } else if (errMessage == null - || !errMessage.matches(expectedMsgPattern)) { + || !Util.toLinux(errMessage).matches(expectedMsgPattern)) { throw new RuntimeException("Error did not match expected [" + expectedMsgPattern + "] while parsing query [" + sap + "]", spe); 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 8c94c1ed4b..b281989593 100644 --- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java +++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java @@ -7602,6 +7602,74 @@ public class SqlOperatorTest { isNullValue(), "INTEGER"); } + @Test void testTimeTrunc() { + SqlOperatorFixture nonBigQuery = fixture() + .setFor(SqlLibraryOperators.TIME_TRUNC); + nonBigQuery.checkFails("^time_trunc(time '15:30:00', hour)^", + "No match found for function signature " + + "TIME_TRUNC\\(<TIME>, <INTERVAL_DAY_TIME>\\)", + false); + + final SqlOperatorFixture f = fixture() + .withLibrary(SqlLibrary.BIG_QUERY) + .setFor(SqlLibraryOperators.TIME_TRUNC); + f.checkFails("time_trunc(time '12:34:56', ^year^)", + "Encountered \"year\" at line 1, column 37\\.\n" + + "Was expecting one of:\n" + + " \"HOUR\" \\.\\.\\.\n" + + " \"MILLISECOND\" \\.\\.\\.\n" + + " \"MINUTE\" \\.\\.\\.\n" + + " \"SECOND\" \\.\\.\\.\n" + + " ", false); + f.checkFails("^time_trunc(123.45, minute)^", + "Cannot apply 'TIME_TRUNC' to arguments of type " + + "'TIME_TRUNC\\(<DECIMAL\\(5, 2\\)>, <INTERVAL MINUTE>\\)'\\. " + + "Supported form\\(s\\): 'TIME_TRUNC\\(<TIME>, <DATETIME_INTERVAL>\\)'", false); + f.checkScalar("time_trunc(time '12:34:56', second)", + "12:34:56", "TIME(0) NOT NULL"); + f.checkScalar("time_trunc(time '12:34:56', minute)", + "12:34:00", "TIME(0) NOT NULL"); + f.checkScalar("time_trunc(time '12:34:56', hour)", + "12:00:00", "TIME(0) NOT NULL"); + f.checkNull("time_trunc(cast(null as time), second)"); + } + + @Test void testTimestampTrunc() { + SqlOperatorFixture nonBigQuery = fixture() + .setFor(SqlLibraryOperators.TIMESTAMP_TRUNC); + nonBigQuery.checkFails("^timestamp_trunc(timestamp '2012-05-02 15:30:00', hour)^", + "No match found for function signature " + + "TIMESTAMP_TRUNC\\(<TIMESTAMP>, <INTERVAL_DAY_TIME>\\)", + false); + + final SqlOperatorFixture f = fixture() + .withLibrary(SqlLibrary.BIG_QUERY) + .setFor(SqlLibraryOperators.TIMESTAMP_TRUNC); + f.checkFails("^timestamp_trunc(100, hour)^", + "Cannot apply 'TIMESTAMP_TRUNC' to arguments of type " + + "'TIMESTAMP_TRUNC\\(<INTEGER>, <INTERVAL HOUR>\\)'\\. " + + "Supported form\\(s\\): 'TIMESTAMP_TRUNC\\(<TIMESTAMP>, <DATETIME_INTERVAL>\\)'", + false); + f.checkFails("timestamp_trunc(timestamp '2015-02-19 12:34:56.78', ^microsecond^)", + "'MICROSECOND' is not a valid datetime format", false); + f.checkFails("timestamp_trunc(timestamp '2015-02-19 12:34:56.78', ^nanosecond^)", + "'NANOSECOND' is not a valid datetime format", false); + f.checkScalar("timestamp_trunc(timestamp '2015-02-19 12:34:56.78', second)", + "2015-02-19 12:34:56", "TIMESTAMP(0) NOT NULL"); + f.checkScalar("timestamp_trunc(timestamp '2015-02-19 12:34:56', minute)", + "2015-02-19 12:34:00", "TIMESTAMP(0) NOT NULL"); + f.checkScalar("timestamp_trunc(timestamp '2015-02-19 12:34:56', hour)", + "2015-02-19 12:00:00", "TIMESTAMP(0) NOT NULL"); + f.checkScalar("timestamp_trunc(timestamp '2015-02-19 12:34:56', day)", + "2015-02-19 00:00:00", "TIMESTAMP(0) NOT NULL"); + f.checkScalar("timestamp_trunc(timestamp '2015-02-19 12:34:56', week)", + "2015-02-15 00:00:00", "TIMESTAMP(0) NOT NULL"); + f.checkScalar("timestamp_trunc(timestamp '2015-02-19 12:34:56', month)", + "2015-02-01 00:00:00", "TIMESTAMP(0) NOT NULL"); + f.checkScalar("timestamp_trunc(timestamp '2015-02-19 12:34:56', year)", + "2015-01-01 00:00:00", "TIMESTAMP(0) NOT NULL"); + } + @Test void testDenseRankFunc() { final SqlOperatorFixture f = fixture(); f.setFor(SqlStdOperatorTable.DENSE_RANK, VM_FENNEL, VM_JAVA);
