[ https://issues.apache.org/jira/browse/FLINK-6813?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16689521#comment-16689521 ]
ASF GitHub Bot commented on FLINK-6813: --------------------------------------- twalthr closed pull request #4117: [FLINK-6813][table]Add TIMESTAMPDIFF supported in SQL URL: https://github.com/apache/flink/pull/4117 This is a PR merged from a forked repository. As GitHub hides the original diff on merge, it is displayed below for the sake of provenance: As this is a foreign pull request (from a fork), the diff is supplied below (as it won't show otherwise due to GitHub magic): diff --git a/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/codegen/CodeGenUtils.scala b/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/codegen/CodeGenUtils.scala index 1d8c926233b..5f144e6fc79 100644 --- a/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/codegen/CodeGenUtils.scala +++ b/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/codegen/CodeGenUtils.scala @@ -289,4 +289,14 @@ object CodeGenUtils { case java.lang.Character.TYPE => s"$fieldTerm.setChar($objectTerm, $valueTerm)" case _ => s"$fieldTerm.set($objectTerm, $valueTerm)" } + + def absFloorRound(value: Double): Long = { + + if (value >= 0) { + java.lang.Math.round(java.lang.Math.floor((value))) + } else { + 0 - java.lang.Math.round(java.lang.Math.floor((java.lang.Math.abs(value)))) + } + + } } diff --git a/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/codegen/CodeGenerator.scala b/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/codegen/CodeGenerator.scala index 52a9dcd0ac6..0ec19aed6b3 100644 --- a/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/codegen/CodeGenerator.scala +++ b/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/codegen/CodeGenerator.scala @@ -1353,7 +1353,7 @@ class CodeGenerator( val right = operands(1) requireTemporal(left) requireTemporal(right) - generateTemporalPlusMinus(plus = true, nullCheck, left, right) + generateTemporalPlusMinus(plus = true, nullCheck, call.`type`.getSqlTypeName, left, right) case MINUS if isNumeric(resultType) => val left = operands.head @@ -1367,7 +1367,7 @@ class CodeGenerator( val right = operands(1) requireTemporal(left) requireTemporal(right) - generateTemporalPlusMinus(plus = false, nullCheck, left, right) + generateTemporalPlusMinus(plus = false, nullCheck, call.`type`.getSqlTypeName, left, right) case MULTIPLY if isNumeric(resultType) => val left = operands.head diff --git a/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/codegen/calls/ScalarOperators.scala b/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/codegen/calls/ScalarOperators.scala index 1af4a3410a6..d23e778fcc1 100644 --- a/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/codegen/calls/ScalarOperators.scala +++ b/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/codegen/calls/ScalarOperators.scala @@ -19,6 +19,7 @@ package org.apache.flink.table.codegen.calls import org.apache.calcite.avatica.util.DateTimeUtils.MILLIS_PER_DAY import org.apache.calcite.avatica.util.{DateTimeUtils, TimeUnitRange} +import org.apache.calcite.sql.`type`.SqlTypeName import org.apache.calcite.util.BuiltInMethod import org.apache.flink.api.common.typeinfo.BasicTypeInfo._ import org.apache.flink.api.common.typeinfo._ @@ -702,12 +703,15 @@ object ScalarOperators { def generateTemporalPlusMinus( plus: Boolean, nullCheck: Boolean, + typeName: SqlTypeName, left: GeneratedExpression, right: GeneratedExpression) : GeneratedExpression = { val op = if (plus) "+" else "-" + val AVGDAYS_PRE_MONTH = 30.5 + (left.resultType, right.resultType) match { case (l: TimeIntervalTypeInfo[_], r: TimeIntervalTypeInfo[_]) if l == r => generateArithmeticOperator(op, nullCheck, l, left, right) @@ -737,6 +741,46 @@ object ScalarOperators { (l, r) => s"${qualifyMethod(BuiltInMethod.ADD_MONTHS.method)}($l, $op($r))" } + case (SqlTimeTypeInfo.TIMESTAMP, SqlTimeTypeInfo.TIMESTAMP) if !plus => + generateOperatorIfNotNull(nullCheck, SqlTimeTypeInfo.TIMESTAMP, left, right) { + (l, r) => typeName match { + case SqlTypeName.INTERVAL_YEAR | SqlTypeName.INTERVAL_MONTH => + s"org.apache.flink.table.codegen.CodeGenUtils." + + s"absFloorRound(($l $op $r)/${MILLIS_PER_DAY}L/${AVGDAYS_PRE_MONTH})" + case _ => s"$l $op $r" + } + } + + case (SqlTimeTypeInfo.DATE, SqlTimeTypeInfo.DATE) if !plus => + generateOperatorIfNotNull(nullCheck, SqlTimeTypeInfo.TIMESTAMP, left, right) { + (l, r) => typeName match { + case SqlTypeName.INTERVAL_YEAR | SqlTypeName.INTERVAL_MONTH => + s"org.apache.flink.table.codegen.CodeGenUtils." + + s"absFloorRound(($l $op $r)/${AVGDAYS_PRE_MONTH})" + case _ => s"($l * ${MILLIS_PER_DAY}L) $op ($r * ${MILLIS_PER_DAY}L)" + } + } + + case (SqlTimeTypeInfo.TIMESTAMP, SqlTimeTypeInfo.DATE) if !plus => + generateOperatorIfNotNull(nullCheck, SqlTimeTypeInfo.TIMESTAMP, left, right) { + (l, r) => typeName match { + case SqlTypeName.INTERVAL_YEAR | SqlTypeName.INTERVAL_MONTH => + s"org.apache.flink.table.codegen.CodeGenUtils." + + s"absFloorRound((($l/${MILLIS_PER_DAY}L) $op $r)/${AVGDAYS_PRE_MONTH})" + case _ => s"$l $op ($r * ${MILLIS_PER_DAY}L)" + } + } + + case (SqlTimeTypeInfo.DATE, SqlTimeTypeInfo.TIMESTAMP) if !plus => + generateOperatorIfNotNull(nullCheck, SqlTimeTypeInfo.TIMESTAMP, left, right) { + (l, r) => typeName match { + case SqlTypeName.INTERVAL_YEAR | SqlTypeName.INTERVAL_MONTH => + s"org.apache.flink.table.codegen.CodeGenUtils." + + s"absFloorRound(($l $op ($r/${MILLIS_PER_DAY}L))/${AVGDAYS_PRE_MONTH})" + case _ => s"($l * ${MILLIS_PER_DAY}L) $op $r" + } + } + case _ => throw new CodeGenException("Unsupported temporal arithmetic.") } diff --git a/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/validate/FunctionCatalog.scala b/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/validate/FunctionCatalog.scala index 4d945a7c66a..9026c9d6ad2 100644 --- a/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/validate/FunctionCatalog.scala +++ b/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/validate/FunctionCatalog.scala @@ -387,6 +387,7 @@ class BasicOperatorTable extends ReflectiveSqlOperatorTable { SqlStdOperatorTable.SIGN, SqlStdOperatorTable.ROUND, SqlStdOperatorTable.PI, + SqlStdOperatorTable.TIMESTAMP_DIFF, // EXTENSIONS SqlStdOperatorTable.TUMBLE, SqlStdOperatorTable.TUMBLE_START, diff --git a/flink-libraries/flink-table/src/test/scala/org/apache/flink/table/expressions/ScalarFunctionsTest.scala b/flink-libraries/flink-table/src/test/scala/org/apache/flink/table/expressions/ScalarFunctionsTest.scala index 76c02c0af50..a74d8c07fa9 100644 --- a/flink-libraries/flink-table/src/test/scala/org/apache/flink/table/expressions/ScalarFunctionsTest.scala +++ b/flink-libraries/flink-table/src/test/scala/org/apache/flink/table/expressions/ScalarFunctionsTest.scala @@ -1465,6 +1465,127 @@ class ScalarFunctionsTest extends ExpressionTestBase { "4") } + @Test + def testTimestampDiff: Unit ={ + + val startTs = ("timestamp '2019-06-01 07:01:11'", "timestamp '2017-2-20 10:10:01.899'") + val endTs = ("timestamp '2020-06-01 07:01:11'", "timestamp '2018-5-24 18:26:53.999'") + + testSqlApi(s"timestampDiff(YEAR, ${startTs._1}, ${endTs._1})", "1") + testSqlApi(s"timestampDiff(SQL_TSI_YEAR, ${startTs._2}, ${endTs._2})", "1") + + testSqlApi(s"timestampDiff(QUARTER, ${startTs._1}, ${endTs._1})", "4") + testSqlApi(s"timestampDiff(SQL_TSI_QUARTER, ${startTs._2}, ${endTs._2})", "5") + + testSqlApi(s"timestampDiff(MONTH, ${startTs._1}, ${endTs._1})", "12") + testSqlApi(s"timestampDiff(SQL_TSI_MONTH, ${startTs._2}, ${endTs._2})", "15") + + testSqlApi(s"timestampDiff(WEEK, ${startTs._1}, ${endTs._1})", "52") + testSqlApi(s"timestampDiff(SQL_TSI_WEEK, ${startTs._2}, ${endTs._2})", "65") + + testSqlApi(s"timestampDiff(DAY, ${startTs._1}, ${endTs._1})", "366") + testSqlApi(s"timestampDiff(SQL_TSI_DAY, ${startTs._2}, ${endTs._2})", "458") + + testSqlApi(s"timestampDiff(HOUR, ${startTs._1}, ${endTs._1})", "8784") + testSqlApi(s"timestampDiff(SQL_TSI_HOUR, ${startTs._2}, ${endTs._2})", "11000") + + testSqlApi(s"timestampDiff(MINUTE, ${startTs._1}, ${endTs._1})", "527040") + testSqlApi(s"timestampDiff(SQL_TSI_MINUTE, ${startTs._2}, ${endTs._2})", "660016") + + testSqlApi(s"timestampDiff(SECOND, ${startTs._1}, ${endTs._1})", "31622400") + testSqlApi(s"timestampDiff(SQL_TSI_SECOND, ${startTs._2}, ${endTs._2})", "39601012") + + testSqlApi( + "timestampDiff(YEAR, timestamp '2012-10-10 22:22:22', timestamp '2013-10-09 22:22:22')","0") + testSqlApi( + "timestampDiff(YEAR, timestamp '2012-10-10 22:22:22', timestamp '2013-10-11 22:22:22')","1") + + testSqlApi( + "timestampDiff(QUARTER, timestamp '2012-9-10 22:22:22', timestamp '2012-12-09 22:22:22')","0") + testSqlApi( + "timestampDiff(QUARTER, timestamp '2012-9-10 22:22:22', timestamp '2012-12-11 22:22:22')","1") + + testSqlApi( + "timestampDiff(MONTH, timestamp '2012-10-10 22:22:22', timestamp '2012-11-09 22:22:22')","0") + testSqlApi( + "timestampDiff(MONTH, timestamp '2012-10-10 22:22:22', timestamp '2012-11-11 22:22:22')","1") + + testSqlApi( + "timestampDiff(DAY, timestamp '2012-10-10 22:22:22', timestamp '2012-10-11 21:22:22')","0") + testSqlApi( + "timestampDiff(DAY, timestamp '2012-10-10 22:22:22', timestamp '2012-10-11 22:22:22')","1") + + testSqlApi( + "timestampDiff(MINUTE, timestamp '2012-10-10 22:22:22', timestamp '2012-10-10 22:23:20')","0") + testSqlApi( + "timestampDiff(MINUTE, timestamp '2012-10-10 22:22:22', timestamp '2012-10-10 22:23:23')","1") + + } + + @Test + def testTimestampDiffWithDateType: Unit ={ + + val startTs = ("date '2019-06-01'", "date '2017-2-20'") + val endTs = ("date '2020-06-01'", "date '2018-5-24'") + + testSqlApi(s"timestampDiff(YEAR, ${startTs._1}, ${endTs._1})", "1") + testSqlApi(s"timestampDiff(SQL_TSI_YEAR, ${startTs._2}, ${endTs._2})", "1") + + testSqlApi(s"timestampDiff(QUARTER, ${startTs._1}, ${endTs._1})", "4") + testSqlApi(s"timestampDiff(SQL_TSI_QUARTER, ${startTs._2}, ${endTs._2})", "5") + + testSqlApi(s"timestampDiff(MONTH, ${startTs._1}, ${endTs._1})", "12") + testSqlApi(s"timestampDiff(SQL_TSI_MONTH, ${startTs._2}, ${endTs._2})", "15") + + testSqlApi(s"timestampDiff(WEEK, ${startTs._1}, ${endTs._1})", "52") + testSqlApi(s"timestampDiff(SQL_TSI_WEEK, ${startTs._2}, ${endTs._2})", "65") + + testSqlApi(s"timestampDiff(DAY, ${startTs._1}, ${endTs._1})", "366") + testSqlApi(s"timestampDiff(SQL_TSI_DAY, ${startTs._2}, ${endTs._2})", "458") + + testSqlApi(s"timestampDiff(HOUR, ${startTs._1}, ${endTs._1})", "8784") + testSqlApi(s"timestampDiff(SQL_TSI_HOUR, ${startTs._2}, ${endTs._2})", "10992") + + testSqlApi(s"timestampDiff(MINUTE, ${startTs._1}, ${endTs._1})", "527040") + testSqlApi(s"timestampDiff(SQL_TSI_MINUTE, ${startTs._2}, ${endTs._2})", "659520") + + testSqlApi(s"timestampDiff(SECOND, ${startTs._1}, ${endTs._1})", "31622400") + testSqlApi(s"timestampDiff(SQL_TSI_SECOND, ${startTs._2}, ${endTs._2})", "39571200") + + } + + @Test + def testTimestampDiffWithMixedType: Unit ={ + + val startTs = ("date '2018-5-24'", "timestamp '2017-2-20 10:10:10.999'") + val endTs = ("timestamp '2017-2-20 10:10:10.999'", "date '2018-5-24'") + + testSqlApi(s"timestampDiff(YEAR, ${startTs._1}, ${endTs._1})", "-1") + testSqlApi(s"timestampDiff(SQL_TSI_YEAR, ${startTs._2}, ${endTs._2})", "1") + + testSqlApi(s"timestampDiff(QUARTER, ${startTs._1}, ${endTs._1})", "-5") + testSqlApi(s"timestampDiff(SQL_TSI_QUARTER, ${startTs._2}, ${endTs._2})", "5") + + testSqlApi(s"timestampDiff(MONTH, ${startTs._1}, ${endTs._1})", "-15") + testSqlApi(s"timestampDiff(SQL_TSI_MONTH, ${startTs._2}, ${endTs._2})", "15") + + testSqlApi(s"timestampDiff(WEEK, ${startTs._1}, ${endTs._1})", "-65") + testSqlApi(s"timestampDiff(SQL_TSI_WEEK, ${startTs._2}, ${endTs._2})", "65") + + testSqlApi(s"timestampDiff(DAY, ${startTs._1}, ${endTs._1})", "-457") + testSqlApi(s"timestampDiff(SQL_TSI_DAY, ${startTs._2}, ${endTs._2})", "457") + + testSqlApi(s"timestampDiff(HOUR, ${startTs._1}, ${endTs._1})", "-10981") + testSqlApi(s"timestampDiff(SQL_TSI_HOUR, ${startTs._2}, ${endTs._2})", "10981") + + testSqlApi(s"timestampDiff(MINUTE, ${startTs._1}, ${endTs._1})", "-658909") + testSqlApi(s"timestampDiff(SQL_TSI_MINUTE, ${startTs._2}, ${endTs._2})", "658909") + + testSqlApi(s"timestampDiff(SECOND, ${startTs._1}, ${endTs._1})", "-39534589") + testSqlApi(s"timestampDiff(SQL_TSI_SECOND, ${startTs._2}, ${endTs._2})", "39534589") + + } + // ---------------------------------------------------------------------------------------------- // Other functions // ---------------------------------------------------------------------------------------------- ---------------------------------------------------------------- This is an automated message from the Apache Git Service. To respond to the message, please log on GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org > Add TIMESTAMPDIFF supported in SQL > ---------------------------------- > > Key: FLINK-6813 > URL: https://issues.apache.org/jira/browse/FLINK-6813 > Project: Flink > Issue Type: Sub-task > Components: Table API & SQL > Affects Versions: 1.4.0 > Reporter: sunjincheng > Assignee: sunjincheng > Priority: Major > Labels: pull-request-available > Fix For: 1.7.0 > > > TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2) Returns datetime_expr2 − > datetime_expr1, where datetime_expr1 and datetime_expr2 are date or datetime > expressions. One expression may be a date and the other a datetime; a date > value is treated as a datetime having the time part '00:00:00' where > necessary. The unit for the result (an integer) is given by the unit > argument. The legal values for unit are the same as those listed in the > description of the TIMESTAMPADD() function. > * Syntax > TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2) > -unit > Is the part of datetime_expr1 and datetime_expr2 that specifies the type of > boundary crossed. > -datetime_expr1 > Is an expression that can be resolved to a time, date. > -datetime_expr2 > Same with startdate. > * Example > SELECT TIMESTAMPDIFF(year, '2015-12-31 23:59:59.9999999', '2017-01-01 > 00:00:00.0000000') from tab; --> 2 > * See more: > > [MySQL|https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_timestampdiff] > CALCITE: > {code} > SELECT timestampdiff(YEAR, timestamp '2019-06-01 07:01:11', timestamp > '2020-06-01 07:01:11'),timestampdiff(QUARTER, timestamp '2019-06-01 > 07:01:11', timestamp '2020-06-01 07:01:11'),timestampdiff(MONTH, timestamp > '2019-06-01 07:01:11',timestamp '2020-06-01 07:01:11'),timestampdiff(WEEK, > timestamp '2019-06-01 07:01:11',timestamp '2020-06-01 > 07:01:11'),timestampdiff(DAY, timestamp '2019-06-01 07:01:11',timestamp > '2020-06-01 07:01:11'),timestampdiff(HOUR, timestamp '2019-06-01 > 07:01:11',timestamp '2020-06-01 07:01:11'),timestampdiff(MINUTE, timestamp > '2019-06-01 07:01:11',timestamp '2020-06-01 07:01:11'),timestampdiff(SECOND, > timestamp '2019-06-01 07:01:11',timestamp '2020-06-01 07:01:11') FROM depts; > | 1 | 4 | 12 | **52** | 366 | 8784 | 527040 | > 31622400 > {code} > MSSQL: > {code} > SELECT > datediff(YEAR, '2019-06-01 07:01:11','2020-06-01 07:01:11'), > datediff(QUARTER, '2019-06-01 07:01:11', '2020-06-01 07:01:11'), > datediff(MONTH, '2019-06-01 07:01:11','2020-06-01 07:01:11'), > datediff(WEEK, '2019-06-01 07:01:11', '2020-06-01 07:01:11'), > datediff(DAY, '2019-06-01 07:01:11','2020-06-01 07:01:11'), > datediff(HOUR, '2019-06-01 07:01:11','2020-06-01 07:01:11'), > datediff(MINUTE, '2019-06-01 07:01:11','2020-06-01 07:01:11'), > datediff(SECOND, '2019-06-01 07:01:11', '2020-06-01 07:01:11') > FROM stu; > |1 |4 |12 |**53** |366 |8784 |527040 |31622400 > {code} > The differences I have discussed with the calcite community. And find the > reason: > https://stackoverflow.com/questions/26138167/is-timestampdiff-in-mysql-equivalent-to-datediff-in-sql-server. > So, In this JIRA. we will keep consistency with calcite. -- This message was sent by Atlassian JIRA (v7.6.3#76005)