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 2022/12/23 23:56:42 UTC
[calcite] branch main updated: [CALCITE-5423] Implement TIMESTAMP_DIFF function (compatible with BigQuery, and enabled in the BigQuery library)
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 bc4cfa3283 [CALCITE-5423] Implement TIMESTAMP_DIFF function (compatible with BigQuery, and enabled in the BigQuery library)
bc4cfa3283 is described below
commit bc4cfa32833649f44f6f2e9455229fa0355ad81e
Author: Tanner Clary <ta...@google.com>
AuthorDate: Wed Dec 7 22:02:01 2022 +0000
[CALCITE-5423] Implement TIMESTAMP_DIFF function (compatible with BigQuery, and enabled in the BigQuery library)
The TIMESTAMP_DIFF function is implemented by
SqlLibraryOperators.TIMESTAMP_DIFF3 and only enabled with
lib=bigquery, whereas the built-in TIMESTAMPDIFF function is
implemented by SqlStdOperatorTable.TIMESTAMP_DIFF and is
always enabled.
Close apache/calcite#3000
---
babel/src/main/codegen/config.fmpp | 1 +
babel/src/test/resources/sql/big-query.iq | 37 ++++---
core/src/main/codegen/default_config.fmpp | 1 +
core/src/main/codegen/templates/Parser.jj | 36 +++++++
.../calcite/sql/fun/SqlLibraryOperators.java | 13 ++-
.../calcite/sql/fun/SqlStdOperatorTable.java | 3 +-
.../calcite/sql/fun/SqlTimestampDiffFunction.java | 43 +++++---
.../calcite/sql2rel/StandardConvertletTable.java | 19 +++-
site/_docs/reference.md | 4 +-
.../org/apache/calcite/test/SqlOperatorTest.java | 120 +++++++++++++++++++++
10 files changed, 238 insertions(+), 39 deletions(-)
diff --git a/babel/src/main/codegen/config.fmpp b/babel/src/main/codegen/config.fmpp
index 2d1ababb42..2b5f7f6b9c 100644
--- a/babel/src/main/codegen/config.fmpp
+++ b/babel/src/main/codegen/config.fmpp
@@ -465,6 +465,7 @@ data: {
# "TIME"
"TIME_TRUNC"
# "TIMESTAMP"
+ "TIMESTAMP_DIFF"
"TIMESTAMP_TRUNC"
"TIMEZONE_HOUR"
"TIMEZONE_MINUTE"
diff --git a/babel/src/test/resources/sql/big-query.iq b/babel/src/test/resources/sql/big-query.iq
index 281604d291..d0c5779fc3 100755
--- a/babel/src/test/resources/sql/big-query.iq
+++ b/babel/src/test/resources/sql/big-query.iq
@@ -1622,43 +1622,42 @@ SELECT
# Display of results may differ, depending upon the environment and
# time zone where this query was executed.
-!if (false) {
SELECT
- TIMESTAMP("2010-07-07 10:20:00+00") AS later_timestamp,
- TIMESTAMP("2008-12-25 15:30:00+00") AS earlier_timestamp,
- TIMESTAMP_DIFF(TIMESTAMP "2010-07-07 10:20:00+00", TIMESTAMP "2008-12-25 15:30:00+00", HOUR) AS hours;
-+-------------------------+-------------------------+-------+
-| later_timestamp | earlier_timestamp | hours |
-+-------------------------+-------------------------+-------+
-| 2010-07-07 10:20:00 UTC | 2008-12-25 15:30:00 UTC | 13410 |
-+-------------------------+-------------------------+-------+
+ TIMESTAMP "2010-07-07 10:20:00" AS later_timestamp,
+ TIMESTAMP "2008-12-25 15:30:00" AS earlier_timestamp,
+ TIMESTAMP_DIFF(TIMESTAMP "2010-07-07 10:20:00", TIMESTAMP "2008-12-25 15:30:00", HOUR) AS hours;
++---------------------+---------------------+-------+
+| later_timestamp | earlier_timestamp | hours |
++---------------------+---------------------+-------+
+| 2010-07-07 10:20:00 | 2008-12-25 15:30:00 | 13410 |
++---------------------+---------------------+-------+
+(1 row)
+
!ok
-!}
# In the following example, the first timestamp occurs
# before the second timestamp, resulting in a negative output.
-!if (false) {
-SELECT TIMESTAMP_DIFF(TIMESTAMP "2018-08-14", TIMESTAMP "2018-10-14", DAY);
+SELECT TIMESTAMP_DIFF(TIMESTAMP "2018-08-14", TIMESTAMP "2018-10-14", DAY) AS negative_diff;
+---------------+
| negative_diff |
+---------------+
-| -61 |
+| -61 |
+---------------+
+(1 row)
+
!ok
-!}
# In this example, the result is 0 because only the number
# of whole specified HOUR intervals are included.
-!if (false) {
-SELECT TIMESTAMP_DIFF("2001-02-01 01:00:00", "2001-02-01 00:00:01", HOUR);
+SELECT TIMESTAMP_DIFF("2001-02-01 01:00:00", "2001-02-01 00:00:01", HOUR) AS negative_diff;
+---------------+
| negative_diff |
+---------------+
-| 0 |
+| 0 |
+---------------+
-!ok
-!}
+(1 row)
+!ok
#####################################################################
# DATE_TRUNC
#
diff --git a/core/src/main/codegen/default_config.fmpp b/core/src/main/codegen/default_config.fmpp
index ea59d6c48d..e27cff2e9a 100644
--- a/core/src/main/codegen/default_config.fmpp
+++ b/core/src/main/codegen/default_config.fmpp
@@ -317,6 +317,7 @@ parser: {
"TIME_TRUNC"
"TIMESTAMPADD"
"TIMESTAMPDIFF"
+ "TIMESTAMP_DIFF"
"TIMESTAMP_TRUNC"
"TOP_LEVEL_COUNT"
"TRANSACTION"
diff --git a/core/src/main/codegen/templates/Parser.jj b/core/src/main/codegen/templates/Parser.jj
index 09eabf64d3..6e1e0e3209 100644
--- a/core/src/main/codegen/templates/Parser.jj
+++ b/core/src/main/codegen/templates/Parser.jj
@@ -6039,6 +6039,8 @@ SqlNode BuiltinFunctionCall() :
node = TimestampAddFunctionCall() { return node; }
|
node = TimestampDiffFunctionCall() { return node; }
+ |
+ node = TimestampDiff3FunctionCall() { return node; }
|
node = TimestampTruncFunctionCall() { return node; }
|
@@ -6588,6 +6590,33 @@ SqlCall TimestampDiffFunctionCall() :
}
}
+/**
+ * Parses a call to BigQuery's TIMESTAMP_DIFF.
+ *
+ * <p>The difference between TIMESTAMPDIFF and TIMESTAMP_DIFF is the ordering of
+ * the parameters and the arrangement of the subtraction.
+ * TIMESTAMPDIFF uses (unit, timestamp1, timestamp2) with (t2 - t1), while
+ * TIMESTAMP_DIFF uses (timestamp1, timestamp2, unit) with (t1 - t2).
+ */
+SqlCall TimestampDiff3FunctionCall() :
+{
+ final List<SqlNode> args = new ArrayList<SqlNode>();
+ final Span s;
+ final SqlIntervalQualifier unit;
+}
+{
+ <TIMESTAMP_DIFF> { s = span(); }
+ <LPAREN>
+ AddExpression(args, ExprContext.ACCEPT_SUB_QUERY)
+ <COMMA>
+ AddExpression(args, ExprContext.ACCEPT_SUB_QUERY)
+ <COMMA>
+ unit = TimeUnitOrName() { args.add(unit); }
+ <RPAREN> {
+ return SqlLibraryOperators.TIMESTAMP_DIFF3.createCall(s.end(this), args);
+ }
+}
+
/**
* Parses a call to TIMESTAMP_TRUNC.
*/
@@ -7132,6 +7161,12 @@ SqlNode JdbcFunctionCall() :
name = call.getOperator().getName();
args = new SqlNodeList(call.getOperandList(), getPos());
}
+ |
+ LOOKAHEAD(1)
+ call = TimestampDiff3FunctionCall() {
+ name = call.getOperator().getName();
+ args = new SqlNodeList(call.getOperandList(), getPos());
+ }
|
LOOKAHEAD(3)
call = TimestampDiffFunctionCall() {
@@ -7977,6 +8012,7 @@ SqlPostfixOperator PostfixRowOperator() :
| < TIMESTAMP: "TIMESTAMP" >
| < TIMESTAMPADD: "TIMESTAMPADD" >
| < TIMESTAMPDIFF: "TIMESTAMPDIFF" >
+| < TIMESTAMP_DIFF: "TIMESTAMP_DIFF" >
| < TIMESTAMP_TRUNC: "TIMESTAMP_TRUNC" >
| < TIMEZONE_HOUR: "TIMEZONE_HOUR" >
| < TIMEZONE_MINUTE: "TIMEZONE_MINUTE" >
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 c7fb48a1a2..43fe6e98ee 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
@@ -107,7 +107,8 @@ public abstract class SqlLibraryOperators {
* argument. */
@LibraryOperator(libraries = {MSSQL, POSTGRESQL})
public static final SqlFunction DATEDIFF =
- new SqlTimestampDiffFunction("DATEDIFF");
+ new SqlTimestampDiffFunction("DATEDIFF",
+ OperandTypes.family(SqlTypeFamily.ANY, SqlTypeFamily.DATE, SqlTypeFamily.DATE));
/** The "DATE_PART(timeUnit, datetime)" function
* (Databricks, Postgres, Redshift, Snowflake). */
@@ -660,6 +661,16 @@ public abstract class SqlLibraryOperators {
OperandTypes.TIMESTAMP_INTERVAL)
.withFunctionType(SqlFunctionCategory.TIMEDATE);
+ /** The "TIMESTAMP_DIFF(timestamp_expression, timestamp_expression, date_time_part)"
+ * function (BigQuery) returns the number of date_time_part between the two timestamp
+ * expressions.
+ *
+ * <p>TIMESTAMP_DIFF(t1, t2, unit) is equivalent to TIMESTAMPDIFF(</p>*/
+ @LibraryOperator(libraries = {BIG_QUERY})
+ public static final SqlFunction TIMESTAMP_DIFF3 =
+ new SqlTimestampDiffFunction("TIMESTAMP_DIFF",
+ OperandTypes.family(SqlTypeFamily.TIMESTAMP, SqlTypeFamily.TIMESTAMP, SqlTypeFamily.ANY));
+
/** 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. */
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
index 8a867471d7..6a1f6403ba 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
@@ -1889,7 +1889,8 @@ public class SqlStdOperatorTable extends ReflectiveSqlOperatorTable {
/** The <code>TIMESTAMPDIFF</code> function. */
public static final SqlFunction TIMESTAMP_DIFF =
- new SqlTimestampDiffFunction("TIMESTAMPDIFF");
+ new SqlTimestampDiffFunction("TIMESTAMPDIFF",
+ OperandTypes.family(SqlTypeFamily.ANY, SqlTypeFamily.TIMESTAMP, SqlTypeFamily.TIMESTAMP));
/**
* Use of the <code>IN_FENNEL</code> operator forces the argument to be
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlTimestampDiffFunction.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlTimestampDiffFunction.java
index eb77639582..a4d3811ecf 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlTimestampDiffFunction.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlTimestampDiffFunction.java
@@ -25,8 +25,7 @@ import org.apache.calcite.sql.SqlFunctionCategory;
import org.apache.calcite.sql.SqlIntervalQualifier;
import org.apache.calcite.sql.SqlKind;
import org.apache.calcite.sql.SqlOperatorBinding;
-import org.apache.calcite.sql.type.OperandTypes;
-import org.apache.calcite.sql.type.SqlTypeFamily;
+import org.apache.calcite.sql.type.SqlOperandTypeChecker;
import org.apache.calcite.sql.type.SqlTypeName;
import org.apache.calcite.sql.validate.SqlValidator;
import org.apache.calcite.sql.validate.SqlValidatorScope;
@@ -61,23 +60,33 @@ import org.apache.calcite.sql.validate.SqlValidatorScope;
class SqlTimestampDiffFunction extends SqlFunction {
private static RelDataType inferReturnType2(SqlOperatorBinding opBinding) {
final RelDataTypeFactory typeFactory = opBinding.getTypeFactory();
- TimeUnit timeUnit = opBinding.getOperandLiteralValue(0, TimeUnit.class);
+ TimeUnit timeUnit;
+ RelDataType type1;
+ RelDataType type2;
+ if (opBinding.isOperandLiteral(0, true)) {
+ type1 = opBinding.getOperandType(0);
+ type2 = opBinding.getOperandType(1);
+ timeUnit = opBinding.getOperandLiteralValue(2, TimeUnit.class);
+ } else {
+ timeUnit = opBinding.getOperandLiteralValue(0, TimeUnit.class);
+ type1 = opBinding.getOperandType(1);
+ type2 = opBinding.getOperandType(2);
+ }
SqlTypeName sqlTypeName =
timeUnit == TimeUnit.NANOSECOND
? SqlTypeName.BIGINT
: SqlTypeName.INTEGER;
return typeFactory.createTypeWithNullability(
typeFactory.createSqlType(sqlTypeName),
- opBinding.getOperandType(1).isNullable()
- || opBinding.getOperandType(2).isNullable());
+ type1.isNullable()
+ || type2.isNullable());
}
/** Creates a SqlTimestampDiffFunction. */
- SqlTimestampDiffFunction(String name) {
+ SqlTimestampDiffFunction(String name, SqlOperandTypeChecker operandTypeChecker) {
super(name, SqlKind.TIMESTAMP_DIFF,
SqlTimestampDiffFunction::inferReturnType2, null,
- OperandTypes.family(SqlTypeFamily.ANY, SqlTypeFamily.DATETIME,
- SqlTypeFamily.DATETIME),
+ operandTypeChecker,
SqlFunctionCategory.TIMEDATE);
}
@@ -87,14 +96,20 @@ class SqlTimestampDiffFunction extends SqlFunction {
// This is either a time unit or a time frame:
//
- // * In "TIMESTAMPADD(YEAR, 2, x)" operand 0 is a SqlIntervalQualifier
- // with startUnit = YEAR and timeFrameName = null.
+ // * In "TIMESTAMPDIFF(YEAR, timestamp1, timestamp2)" operand 0 is a SqlIntervalQualifier
+ // with startUnit = YEAR and timeFrameName = null. The same is true for BigQuery's
+ // TIMESTAMP_DIFF() however the SqlIntervalQualifier is operand 2 due to differing
+ // parameter orders.
//
- // * In "TIMESTAMPADD(MINUTE15, 2, x) operand 0 is a SqlIntervalQualifier
- // with startUnit = EPOCH and timeFrameName = 'MINUTE15'.
+ // * In "TIMESTAMP_ADD(MINUTE15, timestamp1, timestamp2) operand 0 is a SqlIntervalQualifier
+ // with startUnit = EPOCH and timeFrameName = 'MINUTE15'. As above, for BigQuery's
+ // TIMESTAMP_DIFF() the SqlIntervalQualifier is found in operand 2 instead.
//
// If the latter, check that timeFrameName is valid.
- validator.validateTimeFrame(
- (SqlIntervalQualifier) call.getOperandList().get(0));
+ if (call.operand(2) instanceof SqlIntervalQualifier) {
+ validator.validateTimeFrame((SqlIntervalQualifier) call.operand(2));
+ } else {
+ validator.validateTimeFrame((SqlIntervalQualifier) call.operand(0));
+ }
}
}
diff --git a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
index 727ea53910..c6c2dfa098 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
@@ -167,6 +167,8 @@ public class StandardConvertletTable extends ReflectiveConvertletTable {
registerOp(SqlLibraryOperators.TIMESTAMP_ADD2,
new TimestampAddConvertlet());
+ registerOp(SqlLibraryOperators.TIMESTAMP_DIFF3,
+ new TimestampDiffConvertlet());
registerOp(SqlLibraryOperators.NVL, StandardConvertletTable::convertNvl);
registerOp(SqlLibraryOperators.DECODE,
@@ -1898,13 +1900,24 @@ public class StandardConvertletTable extends ReflectiveConvertletTable {
@Override public RexNode convertCall(SqlRexContext cx, SqlCall call) {
// TIMESTAMPDIFF(unit, t1, t2)
// => (t2 - t1) UNIT
+ // TIMESTAMP_DIFF(t1, t2, unit)
+ // => (t1 - t2) UNIT
+ SqlIntervalQualifier qualifier;
+ final RexNode op1;
+ final RexNode op2;
+ if (call.operand(0).getKind() == SqlKind.INTERVAL_QUALIFIER) {
+ qualifier = call.operand(0);
+ op1 = cx.convertExpression(call.operand(1));
+ op2 = cx.convertExpression(call.operand(2));
+ } else {
+ qualifier = call.operand(2);
+ op1 = cx.convertExpression(call.operand(1));
+ op2 = cx.convertExpression(call.operand(0));
+ }
final RexBuilder rexBuilder = cx.getRexBuilder();
- SqlIntervalQualifier qualifier = call.operand(0);
final TimeFrame timeFrame = cx.getValidator().validateTimeFrame(qualifier);
final TimeUnit unit = first(timeFrame.unit(), TimeUnit.EPOCH);
- final RexNode op1 = cx.convertExpression(call.operand(1));
- final RexNode op2 = cx.convertExpression(call.operand(2));
if (unit == TimeUnit.EPOCH && qualifier.timeFrameName != null) {
// Custom time frames have a different path. They are kept as names, and
// then handled by Java functions.
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 423fd338c3..319b13ced8 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -1017,6 +1017,7 @@ TIES,
**TIMESTAMP**,
TIMESTAMPADD,
TIMESTAMPDIFF,
+TIMESTAMP_DIFF,
TIMESTAMP_TRUNC,
**TIMEZONE_HOUR**,
**TIMEZONE_MINUTE**,
@@ -2655,7 +2656,8 @@ semantics.
| m | STRCMP(string, string) | Returns 0 if both of the strings are same and returns -1 when the first argument is smaller than the second and 1 when the second one is smaller than the first one
| b m o p | SUBSTR(string, position [, substringLength ]) | Returns a portion of *string*, beginning at character *position*, *substringLength* characters long. SUBSTR calculates lengths using characters as defined by the input character set
| b o | TANH(numeric) | Returns the hyperbolic tangent of *numeric*
-| b | TIMESTAMP_ADD(timestamp, interval int64 date_part) | Adds int64_expression units of date_part to the timestamp, independent of any time zone.
+| b | TIMESTAMP_ADD(timestamp, interval) | Adds *interval* to *timestamp*, independent of any time zone
+| b | TIMESTAMP_DIFF(timestamp, timestamp2, timeUnit) | Returns the whole number of *timeUnit* between *timestamp* and *timestamp2*. Equivalent to `TIMESTAMPDIFF(timeUnit, timestamp2, timestamp)` and `(timestamp - timestamp2) timeUnit`
| 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
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 92cec17ff3..465110e9d6 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -7582,6 +7582,11 @@ public class SqlOperatorTest {
f.checkScalar("timestampdiff(\"month4\", date '2016-02-24', "
+ "date '2016-02-23')",
"0", "INTEGER NOT NULL");
+ f.withLibrary(SqlLibrary.BIG_QUERY)
+ .setFor(SqlLibraryOperators.TIMESTAMP_DIFF3)
+ .checkScalar("timestamp_diff(timestamp '2008-12-25 15:30:00', "
+ + "timestamp '2008-12-25 16:30:00', \"minute15\")",
+ "-4", "INTEGER NOT NULL");
}
@Test void testFloorFuncInterval() {
@@ -7816,6 +7821,121 @@ public class SqlOperatorTest {
f.checkNull("timestamp_add(CAST(NULL AS TIMESTAMP), interval 5 minute)");
}
+ /** Tests {@code TIMESTAMP_DIFF}, BigQuery's variant of the
+ * {@code TIMESTAMPDIFF} function, which differs in the ordering
+ * of the parameters and the ordering of the subtraction between
+ * the two timestamps. In {@code TIMESTAMPDIFF} it is (t2 - t1)
+ * while for {@code TIMESTAMP_DIFF} is is (t1 - t2). */
+ @Test void testTimestampDiff3() {
+ final SqlOperatorFixture f0 = fixture()
+ .setFor(SqlLibraryOperators.TIMESTAMP_DIFF3);
+ f0.checkFails("^timestamp_diff(timestamp '2008-12-25 15:30:00', "
+ + "timestamp '2008-12-25 16:30:00', "
+ + "minute)^",
+ "No match found for function signature "
+ + "TIMESTAMP_DIFF\\(<TIMESTAMP>, <TIMESTAMP>, <INTERVAL_DAY_TIME>\\)", false);
+
+ final SqlOperatorFixture f = fixture()
+ .withLibrary(SqlLibrary.BIG_QUERY)
+ .setFor(SqlLibraryOperators.TIMESTAMP_DIFF3);
+ HOUR_VARIANTS.forEach(s ->
+ f.checkScalar("timestamp_diff(timestamp '2016-02-24 12:42:25', "
+ + "timestamp '2016-02-24 15:42:25', "
+ + s + ")",
+ "-3", "INTEGER NOT NULL"));
+ MICROSECOND_VARIANTS.forEach(s ->
+ f.checkScalar("timestamp_diff(timestamp '2016-02-24 12:42:25', "
+ + "timestamp '2016-02-24 12:42:20', "
+ + s + ")",
+ "5000000", "INTEGER NOT NULL"));
+ YEAR_VARIANTS.forEach(s ->
+ f.checkScalar("timestamp_diff(timestamp '2014-02-24 12:42:25', "
+ + "timestamp '2016-02-24 12:42:25', "
+ + s + ")",
+ "-2", "INTEGER NOT NULL"));
+ WEEK_VARIANTS.forEach(s ->
+ f.checkScalar("timestamp_diff(timestamp '2014-02-24 12:42:25', "
+ + "timestamp '2016-02-24 12:42:25', "
+ + s + ")",
+ "-104", "INTEGER NOT NULL"));
+ WEEK_VARIANTS.forEach(s ->
+ f.checkScalar("timestamp_diff(timestamp '2014-02-19 12:42:25', "
+ + "timestamp '2016-02-24 12:42:25', "
+ + s + ")",
+ "-105", "INTEGER NOT NULL"));
+ MONTH_VARIANTS.forEach(s ->
+ f.checkScalar("timestamp_diff(timestamp '2014-02-24 12:42:25', "
+ + "timestamp '2016-02-24 12:42:25', "
+ + s + ")",
+ "-24", "INTEGER NOT NULL"));
+ MONTH_VARIANTS.forEach(s ->
+ f.checkScalar("timestamp_diff(timestamp '2019-09-01 12:42:25', "
+ + "timestamp '2020-03-01 12:42:25', "
+ + s + ")",
+ "-6", "INTEGER NOT NULL"));
+ MONTH_VARIANTS.forEach(s ->
+ f.checkScalar("timestamp_diff(timestamp '2019-09-01 12:42:25', "
+ + "timestamp '2016-08-01 12:42:25', "
+ + s + ")",
+ "37", "INTEGER NOT NULL"));
+ QUARTER_VARIANTS.forEach(s ->
+ f.checkScalar("timestamp_diff(timestamp '2014-02-24 12:42:25', "
+ + "timestamp '2016-02-24 12:42:25', "
+ + s + ")",
+ "-8", "INTEGER NOT NULL"));
+ f.checkScalar("timestamp_diff(timestamp '2014-02-24 12:42:25', "
+ + "timestamp '2614-02-24 12:42:25', "
+ + "CENTURY)",
+ "-6", "INTEGER NOT NULL");
+ QUARTER_VARIANTS.forEach(s ->
+ f.checkScalar("timestamp_diff(timestamp '2016-02-24 12:42:25', "
+ + "cast(null as timestamp), "
+ + s + ")",
+ isNullValue(), "INTEGER"));
+
+ // timestamp_diff with date
+ MONTH_VARIANTS.forEach(s ->
+ f.checkScalar("timestamp_diff(date '2016-03-15', "
+ + "date '2016-06-14', "
+ + s + ")",
+ "-2", "INTEGER NOT NULL"));
+ MONTH_VARIANTS.forEach(s ->
+ f.checkScalar("timestamp_diff(date '2019-09-01', "
+ + "date '2020-03-01', "
+ + s + ")",
+ "-6", "INTEGER NOT NULL"));
+ MONTH_VARIANTS.forEach(s ->
+ f.checkScalar("timestamp_diff(date '2019-09-01', "
+ + "date '2016-08-01', "
+ + s + ")",
+ "37", "INTEGER NOT NULL"));
+ DAY_VARIANTS.forEach(s ->
+ f.checkScalar("timestamp_diff(date '2016-06-15', "
+ + "date '2016-06-14', "
+ + s + ")",
+ "1", "INTEGER NOT NULL"));
+ HOUR_VARIANTS.forEach(s ->
+ f.checkScalar("timestamp_diff(date '2016-06-15', "
+ + "date '2016-06-14', "
+ + s + ")",
+ "24", "INTEGER NOT NULL"));
+ HOUR_VARIANTS.forEach(s ->
+ f.checkScalar("timestamp_diff(date '2016-06-15', "
+ + "date '2016-06-15', "
+ + s + ")",
+ "0", "INTEGER NOT NULL"));
+ MINUTE_VARIANTS.forEach(s ->
+ f.checkScalar("timestamp_diff(date '2016-06-15', "
+ + "date '2016-06-14', "
+ + s + ")",
+ "1440", "INTEGER NOT NULL"));
+ DAY_VARIANTS.forEach(s ->
+ f.checkScalar("timestamp_diff(date '2016-06-15', "
+ + "cast(null as date), "
+ + s + ")",
+ isNullValue(), "INTEGER"));
+ }
+
@Test void testTimestampDiff() {
final SqlOperatorFixture f = fixture();
f.setFor(SqlStdOperatorTable.TIMESTAMP_DIFF, VmName.EXPAND);