You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@spark.apache.org by we...@apache.org on 2025/07/31 06:07:00 UTC
(spark) branch master updated: [SPARK-51555][SQL] Add the time_diff() function
This is an automated email from the ASF dual-hosted git repository.
wenchen pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/master by this push:
new b03a86923c0d [SPARK-51555][SQL] Add the time_diff() function
b03a86923c0d is described below
commit b03a86923c0daf9574dd484b68d91ba3493df708
Author: Uros Bojanic <ur...@databricks.com>
AuthorDate: Thu Jul 31 14:06:44 2025 +0800
[SPARK-51555][SQL] Add the time_diff() function
### What changes were proposed in this pull request?
Implement the `time_diff` function, which returns the difference between two times measured in units.
Syntax
```
time_diff(unit, start, end)
unit
{ MICROSECOND |
MILLISECOND |
SECOND |
MINUTE |
HOUR }
```
Arguments
- `unit`: A unit of measure.
- `start`: A starting TIME expression.
- `end`: An ending TIME expression.
Returns
An INTEGER.
If start is greater than end the result is negative.
### Why are the changes needed?
Users will be able to calculate time difference in specified units.
### Does this PR introduce _any_ user-facing change?
Yes, this PR introduces a new expression.
### How was this patch tested?
New unit tests for time truncation, catalyst expression level unit tests with & without codegen, e2e SQL tests / golden files in both ANSI and non-ANSI modes.
### Was this patch authored or co-authored using generative AI tooling?
No.
Closes #51715 from uros-db/timediff.
Authored-by: Uros Bojanic <ur...@databricks.com>
Signed-off-by: Wenchen Fan <we...@databricks.com>
---
.../src/main/resources/error/error-conditions.json | 2 +-
.../sql/catalyst/analysis/FunctionRegistry.scala | 1 +
.../sql/catalyst/expressions/timeExpressions.scala | 68 ++-
.../spark/sql/catalyst/util/DateTimeUtils.scala | 39 +-
.../spark/sql/errors/QueryExecutionErrors.scala | 6 +-
.../expressions/TimeExpressionsSuite.scala | 49 +-
.../sql/catalyst/util/DateTimeUtilsSuite.scala | 56 ++
.../sql-functions/sql-expression-schema.md | 1 +
.../sql-tests/analyzer-results/time.sql.out | 519 ++++++++++++++++
.../src/test/resources/sql-tests/inputs/time.sql | 89 +++
.../test/resources/sql-tests/results/time.sql.out | 659 ++++++++++++++++++++-
11 files changed, 1473 insertions(+), 16 deletions(-)
diff --git a/common/utils/src/main/resources/error/error-conditions.json b/common/utils/src/main/resources/error/error-conditions.json
index febc92eba902..e4482342d483 100644
--- a/common/utils/src/main/resources/error/error-conditions.json
+++ b/common/utils/src/main/resources/error/error-conditions.json
@@ -3436,7 +3436,7 @@
"expects a string literal, but got <invalidValue>."
]
},
- "TIMETRUNC_UNIT" : {
+ "TIME_UNIT" : {
"message" : [
"expects one of the units 'HOUR', 'MINUTE', 'SECOND', 'MILLISECOND', 'MICROSECOND', but got '<invalidValue>'."
]
diff --git a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/FunctionRegistry.scala b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/FunctionRegistry.scala
index 572165e87961..2cf85cb7e30c 100644
--- a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/FunctionRegistry.scala
+++ b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/FunctionRegistry.scala
@@ -655,6 +655,7 @@ object FunctionRegistry {
expressionBuilder("second", SecondExpressionBuilder),
expression[ParseToTimestamp]("to_timestamp"),
expression[ParseToDate]("to_date"),
+ expression[TimeDiff]("time_diff"),
expression[ToTime]("to_time"),
expression[ToBinary]("to_binary"),
expression[ToUnixTimestamp]("to_unix_timestamp"),
diff --git a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/timeExpressions.scala b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/timeExpressions.scala
index 0f868f4e98dc..ff088876969b 100644
--- a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/timeExpressions.scala
+++ b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/timeExpressions.scala
@@ -33,7 +33,7 @@ import org.apache.spark.sql.catalyst.util.TimeFormatter
import org.apache.spark.sql.catalyst.util.TypeUtils.ordinalNumber
import org.apache.spark.sql.errors.{QueryCompilationErrors, QueryExecutionErrors}
import org.apache.spark.sql.internal.types.StringTypeWithCollation
-import org.apache.spark.sql.types.{AbstractDataType, AnyTimeType, ByteType, DataType, DayTimeIntervalType, DecimalType, IntegerType, ObjectType, TimeType}
+import org.apache.spark.sql.types.{AbstractDataType, AnyTimeType, ByteType, DataType, DayTimeIntervalType, DecimalType, IntegerType, LongType, ObjectType, TimeType}
import org.apache.spark.sql.types.DayTimeIntervalType.{HOUR, SECOND}
import org.apache.spark.unsafe.types.UTF8String
@@ -631,6 +631,72 @@ case class SubtractTimes(left: Expression, right: Expression)
copy(left = newLeft, right = newRight)
}
+/**
+ * Returns the difference between two times, measured in specified units.
+ */
+// scalastyle:off line.size.limit
+@ExpressionDescription(
+ usage = """
+ _FUNC_(unit, start, end) - Gets the difference between the times in the specified units.
+ """,
+ arguments = """
+ Arguments:
+ * unit - the unit of the difference between the given times
+ - "HOUR"
+ - "MINUTE"
+ - "SECOND"
+ - "MILLISECOND"
+ - "MICROSECOND"
+ * start - a starting TIME expression
+ * end - an ending TIME expression
+ """,
+ examples = """
+ Examples:
+ > SELECT _FUNC_('HOUR', TIME'20:30:29', TIME'21:30:28');
+ 0
+ > SELECT _FUNC_('HOUR', TIME'20:30:29', TIME'21:30:29');
+ 1
+ > SELECT _FUNC_('HOUR', TIME'20:30:29', TIME'12:00:00');
+ -8
+ """,
+ group = "datetime_funcs",
+ since = "4.1.0")
+// scalastyle:on line.size.limit
+case class TimeDiff(
+ unit: Expression,
+ start: Expression,
+ end: Expression)
+ extends TernaryExpression
+ with RuntimeReplaceable
+ with ImplicitCastInputTypes {
+
+ override def first: Expression = unit
+ override def second: Expression = start
+ override def third: Expression = end
+
+ override def inputTypes: Seq[AbstractDataType] =
+ Seq(StringTypeWithCollation(supportsTrimCollation = true), AnyTimeType, AnyTimeType)
+
+ override def dataType: DataType = LongType
+
+ override def prettyName: String = "time_diff"
+
+ override protected def withNewChildrenInternal(
+ newUnit: Expression, newStart: Expression, newEnd: Expression): TimeDiff = {
+ copy(unit = newUnit, start = newStart, end = newEnd)
+ }
+
+ override def replacement: Expression = {
+ StaticInvoke(
+ classOf[DateTimeUtils.type],
+ dataType,
+ "timeDiff",
+ Seq(unit, start, end),
+ Seq(unit.dataType, start.dataType, end.dataType)
+ )
+ }
+}
+
// scalastyle:off line.size.limit
@ExpressionDescription(
usage = """
diff --git a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/DateTimeUtils.scala b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/DateTimeUtils.scala
index 60a3285eb440..d63e3f91e898 100644
--- a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/DateTimeUtils.scala
+++ b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/DateTimeUtils.scala
@@ -520,7 +520,7 @@ object DateTimeUtils extends SparkDateTimeUtils {
case "MILLISECOND" => ChronoUnit.MILLIS
case "MICROSECOND" => ChronoUnit.MICROS
case _ =>
- throw QueryExecutionErrors.invalidTimeTruncUnitError("time_trunc", level.toString)
+ throw QueryExecutionErrors.invalidTimeUnitError("time_trunc", level.toString)
}
}
@@ -716,6 +716,43 @@ object DateTimeUtils extends SparkDateTimeUtils {
(endNanos - startNanos) / NANOS_PER_MICROS
}
+ // Helper method to get the number of nanoseconds per the given time unit, used for calculating
+ // the difference between two time values (timediff function). Supported units are: MICROSECOND,
+ // MILLISECOND, SECOND, MINUTE, HOUR.
+ private def getNanosPerTimeUnit(unit: UTF8String): Long = {
+ val unitStr = unit.toString
+ unitStr.toUpperCase(Locale.ROOT) match {
+ case "MICROSECOND" =>
+ NANOS_PER_MICROS
+ case "MILLISECOND" =>
+ NANOS_PER_MILLIS
+ case "SECOND" =>
+ NANOS_PER_SECOND
+ case "MINUTE" =>
+ NANOS_PER_SECOND * SECONDS_PER_MINUTE
+ case "HOUR" =>
+ NANOS_PER_SECOND * SECONDS_PER_MINUTE * MINUTES_PER_HOUR
+ case _ =>
+ throw QueryExecutionErrors.invalidTimeUnitError("timediff", unitStr)
+ }
+ }
+
+ /**
+ * Gets the difference between two time values in the specified unit.
+ *
+ * @param unit Specifies the interval units in which to express the difference between
+ * the two time parameters. Supported units are: MICROSECOND, MILLISECOND,
+ * SECOND, MINUTE, HOUR.
+ * @param startNanos A time value expressed as nanoseconds since the start of the day,
+ * which the function subtracts from `endNanos`.
+ * @param endNanos A time value expressed as nanoseconds since the start of the day,
+ * from which the function subtracts `startNanos`.
+ * @return The time span between two time values, in the units specified.
+ */
+ def timeDiff(unit: UTF8String, startNanos: Long, endNanos: Long): Long = {
+ (endNanos - startNanos) / getNanosPerTimeUnit(unit)
+ }
+
/**
* Subtracts two timestamps expressed as microseconds since 1970-01-01 00:00:00Z, and returns
* the difference in microseconds between local timestamps at the given time zone.
diff --git a/sql/catalyst/src/main/scala/org/apache/spark/sql/errors/QueryExecutionErrors.scala b/sql/catalyst/src/main/scala/org/apache/spark/sql/errors/QueryExecutionErrors.scala
index a320bb334e3c..ba229a2e746c 100644
--- a/sql/catalyst/src/main/scala/org/apache/spark/sql/errors/QueryExecutionErrors.scala
+++ b/sql/catalyst/src/main/scala/org/apache/spark/sql/errors/QueryExecutionErrors.scala
@@ -3067,13 +3067,13 @@ private[sql] object QueryExecutionErrors extends QueryErrorsBase with ExecutionE
)
}
- // Throws a SparkIllegalArgumentException when an invalid time truncation unit is specified.
+ // Throws a SparkIllegalArgumentException when an invalid time unit is specified.
// Note that the supported units are: HOUR, MINUTE, SECOND, MILLISECOND, MICROSECOND.
- def invalidTimeTruncUnitError(
+ def invalidTimeUnitError(
functionName: String,
invalidValue: String): Throwable = {
new SparkIllegalArgumentException(
- errorClass = "INVALID_PARAMETER_VALUE.TIMETRUNC_UNIT",
+ errorClass = "INVALID_PARAMETER_VALUE.TIME_UNIT",
messageParameters = Map(
"functionName" -> toSQLId(functionName),
"parameter" -> toSQLId("unit"),
diff --git a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/TimeExpressionsSuite.scala b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/TimeExpressionsSuite.scala
index 44dacccf1074..bccebf309b0c 100644
--- a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/TimeExpressionsSuite.scala
+++ b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/TimeExpressionsSuite.scala
@@ -395,6 +395,53 @@ class TimeExpressionsSuite extends SparkFunSuite with ExpressionEvalHelper {
TimeType(), DayTimeIntervalType())
}
+ test("SPARK-51555: Time difference") {
+ // Test cases for various difference units - from 09:32:05.359123 until 17:23:49.906152.
+ val startTime: Long = localTime(9, 32, 5, 359123)
+ val startTimeLit: Expression = Literal(startTime, TimeType())
+ val endTime: Long = localTime(17, 23, 49, 906152)
+ val endTimeLit: Expression = Literal(endTime, TimeType())
+
+ // Test differences for valid units.
+ checkEvaluation(TimeDiff(Literal("HOUR"), startTimeLit, endTimeLit), 7L)
+ checkEvaluation(TimeDiff(Literal("MINUTE"), startTimeLit, endTimeLit), 471L)
+ checkEvaluation(TimeDiff(Literal("SECOND"), startTimeLit, endTimeLit), 28304L)
+ checkEvaluation(TimeDiff(Literal("MILLISECOND"), startTimeLit, endTimeLit), 28304547L)
+ checkEvaluation(TimeDiff(Literal("MICROSECOND"), startTimeLit, endTimeLit), 28304547029L)
+
+ // Test case-insensitive units.
+ checkEvaluation(TimeDiff(Literal("hour"), startTimeLit, endTimeLit), 7L)
+ checkEvaluation(TimeDiff(Literal("Minute"), startTimeLit, endTimeLit), 471L)
+ checkEvaluation(TimeDiff(Literal("seconD"), startTimeLit, endTimeLit), 28304L)
+ checkEvaluation(TimeDiff(Literal("milliSECOND"), startTimeLit, endTimeLit), 28304547L)
+ checkEvaluation(TimeDiff(Literal("mIcRoSeCoNd"), startTimeLit, endTimeLit), 28304547029L)
+
+ // Test invalid units.
+ val invalidUnits: Seq[String] = Seq("MS", "INVALID", "ABC", "XYZ", " ", "")
+ invalidUnits.foreach { unit =>
+ checkErrorInExpression[SparkIllegalArgumentException](
+ TimeDiff(Literal(unit), startTimeLit, endTimeLit),
+ condition = "INVALID_PARAMETER_VALUE.TIME_UNIT",
+ parameters = Map(
+ "functionName" -> "`timediff`",
+ "parameter" -> "`unit`",
+ "invalidValue" -> s"'$unit'"
+ )
+ )
+ }
+
+ // Test null inputs.
+ val nullUnit = Literal.create(null, StringType)
+ val nullTime = Literal.create(null, TimeType())
+ checkEvaluation(TimeDiff(nullUnit, startTimeLit, endTimeLit), null)
+ checkEvaluation(TimeDiff(Literal("hour"), nullTime, endTimeLit), null)
+ checkEvaluation(TimeDiff(Literal("hour"), startTimeLit, nullTime), null)
+ checkEvaluation(TimeDiff(nullUnit, nullTime, endTimeLit), null)
+ checkEvaluation(TimeDiff(nullUnit, startTimeLit, nullTime), null)
+ checkEvaluation(TimeDiff(Literal("hour"), nullTime, nullTime), null)
+ checkEvaluation(TimeDiff(nullUnit, nullTime, nullTime), null)
+ }
+
test("Subtract times") {
checkEvaluation(
SubtractTimes(Literal.create(null, TimeType()), Literal(LocalTime.MIN)),
@@ -471,7 +518,7 @@ class TimeExpressionsSuite extends SparkFunSuite with ExpressionEvalHelper {
exception = intercept[SparkIllegalArgumentException] {
TimeTrunc(Literal(unit), Literal(testTime, TimeType())).eval()
},
- condition = "INVALID_PARAMETER_VALUE.TIMETRUNC_UNIT",
+ condition = "INVALID_PARAMETER_VALUE.TIME_UNIT",
parameters = Map(
"functionName" -> "`time_trunc`",
"parameter" -> "`unit`",
diff --git a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/util/DateTimeUtilsSuite.scala b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/util/DateTimeUtilsSuite.scala
index 5862d394653f..b5b69a834037 100644
--- a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/util/DateTimeUtilsSuite.scala
+++ b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/util/DateTimeUtilsSuite.scala
@@ -1451,6 +1451,62 @@ class DateTimeUtilsSuite extends SparkFunSuite with Matchers with SQLHelper {
)
}
+ // Helper methods to assert results of the timeDiff method and verify execution symmetry.
+ private def testTimeDiff(unit: String, start: Long, end: Long, expected: Long): Unit = {
+ val unitStr = UTF8String.fromString(unit)
+ assert(timeDiff(unitStr, start, end) === expected)
+ assert(timeDiff(unitStr, end, start) === -expected)
+ }
+
+ test("SPARK-51555: time difference calculation using timeDiff") {
+ // Helper variables to express various units of time in nanoseconds.
+ val zero = 0L
+ val nano = zero + 1
+ val micro = 1000 * nano
+ val milli = 1000 * micro
+ val sec = 1000 * milli
+ val min = 60 * sec
+ val hour = 60 * min
+ val day = 24 * hour
+ val maxTime = day - nano
+
+ // Tests that return the same results for all supported units.
+ val supportedUnits = Seq("HOUR", "MINUTE", "SECOND", "MILLISECOND", "MICROSECOND")
+ supportedUnits.foreach(unit => {
+ testTimeDiff(unit, zero, zero, 0)
+ testTimeDiff(unit, zero, nano, 0)
+ testTimeDiff(unit, zero, nano * 999, 0)
+ testTimeDiff(unit, nano, nano, 0)
+ testTimeDiff(unit, nano, nano * 999, 0)
+ testTimeDiff(unit, maxTime, maxTime, 0)
+ testTimeDiff(unit, maxTime, maxTime - 999, 0)
+ })
+
+ // Tests that return different results for various supported units.
+ testTimeDiff("HOUR", hour, hour, 0)
+ testTimeDiff("MINUTE", min, min, 0)
+ testTimeDiff("SECOND", sec, sec, 0)
+ testTimeDiff("MILLISECOND", milli, milli, 0)
+ testTimeDiff("MICROSECOND", micro, micro, 0)
+ testTimeDiff("HOUR", zero, hour, 1)
+ testTimeDiff("MINUTE", zero, min, 1)
+ testTimeDiff("SECOND", zero, sec, 1)
+ testTimeDiff("MILLISECOND", zero, milli, 1)
+ testTimeDiff("MICROSECOND", zero, micro, 1)
+ testTimeDiff("HOUR", zero, maxTime, 23)
+ testTimeDiff("MINUTE", zero, maxTime, 1439)
+ testTimeDiff("SECOND", zero, maxTime, 86399)
+ testTimeDiff("MILLISECOND", zero, maxTime, 86399999)
+ testTimeDiff("MICROSECOND", zero, maxTime, 86399999999L)
+ val start = 10 * hour + 53 * min + 45 * sec + 123 * milli + 456 * micro // 10:53:45.123456
+ val end = 11 * hour + 54 * min + 46 * sec + 654 * milli + 321 * micro // 11:54:46.654321
+ testTimeDiff("HOUR", start, end, 1)
+ testTimeDiff("MINUTE", start, end, 61)
+ testTimeDiff("SECOND", start, end, 3661)
+ testTimeDiff("MILLISECOND", start, end, 3661530)
+ testTimeDiff("MICROSECOND", start, end, 3661530865L)
+ }
+
test("subtract times") {
Seq(
(LocalTime.MIDNIGHT, LocalTime.MIDNIGHT) -> 0,
diff --git a/sql/core/src/test/resources/sql-functions/sql-expression-schema.md b/sql/core/src/test/resources/sql-functions/sql-expression-schema.md
index 7b5c2c826470..6e6d520efbac 100644
--- a/sql/core/src/test/resources/sql-functions/sql-expression-schema.md
+++ b/sql/core/src/test/resources/sql-functions/sql-expression-schema.md
@@ -342,6 +342,7 @@
| org.apache.spark.sql.catalyst.expressions.Subtract | - | SELECT 2 - 1 | struct<(2 - 1):int> |
| org.apache.spark.sql.catalyst.expressions.Tan | tan | SELECT tan(0) | struct<TAN(0):double> |
| org.apache.spark.sql.catalyst.expressions.Tanh | tanh | SELECT tanh(0) | struct<TANH(0):double> |
+| org.apache.spark.sql.catalyst.expressions.TimeDiff | time_diff | SELECT time_diff('HOUR', TIME'20:30:29', TIME'21:30:28') | struct<time_diff(HOUR, TIME '20:30:29', TIME '21:30:28'):bigint> |
| org.apache.spark.sql.catalyst.expressions.TimeTrunc | time_trunc | SELECT time_trunc('HOUR', TIME'09:32:05.359') | struct<time_trunc(HOUR, TIME '09:32:05.359'):time(6)> |
| org.apache.spark.sql.catalyst.expressions.TimeWindow | window | SELECT a, window.start, window.end, count(*) as cnt FROM VALUES ('A1', '2021-01-01 00:00:00'), ('A1', '2021-01-01 00:04:30'), ('A1', '2021-01-01 00:06:00'), ('A2', '2021-01-01 00:01:00') AS tab(a, b) GROUP by a, window(b, '5 minutes') ORDER BY a, start | struct<a:string,start:timestamp,end:timestamp,cnt:bigint> |
| org.apache.spark.sql.catalyst.expressions.ToBinary | to_binary | SELECT to_binary('abc', 'utf-8') | struct<to_binary(abc, utf-8):binary> |
diff --git a/sql/core/src/test/resources/sql-tests/analyzer-results/time.sql.out b/sql/core/src/test/resources/sql-tests/analyzer-results/time.sql.out
index ffaead8ba78d..807087a92c02 100644
--- a/sql/core/src/test/resources/sql-tests/analyzer-results/time.sql.out
+++ b/sql/core/src/test/resources/sql-tests/analyzer-results/time.sql.out
@@ -1,4 +1,12 @@
-- Automatically generated by SQLQueryTestSuite
+-- !query
+create temporary view timediff_view as select time'01:02:03' time_start, time'04:05:06' time_end, 'SECOND' unit
+-- !query analysis
+CreateViewCommand `timediff_view`, select time'01:02:03' time_start, time'04:05:06' time_end, 'SECOND' unit, false, false, LocalTempView, UNSUPPORTED, true
+ +- Project [01:02:03 AS time_start#x, 04:05:06 AS time_end#x, SECOND AS unit#x]
+ +- OneRowRelation
+
+
-- !query
create temporary view time_view as select '11:53:26.038344' time_str, 'HH:mm:ss.SSSSSS' fmt_str
-- !query analysis
@@ -990,6 +998,517 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
}
+-- !query
+SELECT time_diff('HOUR', time'00:00:00', time'12:34:56')
+-- !query analysis
+Project [time_diff(HOUR, 00:00:00, 12:34:56) AS time_diff(HOUR, TIME '00:00:00', TIME '12:34:56')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('MINUTE', time'00:00:00', time'12:34:56')
+-- !query analysis
+Project [time_diff(MINUTE, 00:00:00, 12:34:56) AS time_diff(MINUTE, TIME '00:00:00', TIME '12:34:56')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('SECOND', time'00:00:00', time'12:34:56')
+-- !query analysis
+Project [time_diff(SECOND, 00:00:00, 12:34:56) AS time_diff(SECOND, TIME '00:00:00', TIME '12:34:56')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('MILLISECOND', time'00:00:00', time'12:34:56')
+-- !query analysis
+Project [time_diff(MILLISECOND, 00:00:00, 12:34:56) AS time_diff(MILLISECOND, TIME '00:00:00', TIME '12:34:56')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('MICROSECOND', time'00:00:00', time'12:34:56')
+-- !query analysis
+Project [time_diff(MICROSECOND, 00:00:00, 12:34:56) AS time_diff(MICROSECOND, TIME '00:00:00', TIME '12:34:56')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('HOUR', time'01:02:03', time'12:34:56')
+-- !query analysis
+Project [time_diff(HOUR, 01:02:03, 12:34:56) AS time_diff(HOUR, TIME '01:02:03', TIME '12:34:56')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('MINUTE', time'01:02:03', time'12:34:56')
+-- !query analysis
+Project [time_diff(MINUTE, 01:02:03, 12:34:56) AS time_diff(MINUTE, TIME '01:02:03', TIME '12:34:56')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('SECOND', time'01:02:03', time'12:34:56')
+-- !query analysis
+Project [time_diff(SECOND, 01:02:03, 12:34:56) AS time_diff(SECOND, TIME '01:02:03', TIME '12:34:56')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('HOUR', time'12:34:56', time'01:02:03')
+-- !query analysis
+Project [time_diff(HOUR, 12:34:56, 01:02:03) AS time_diff(HOUR, TIME '12:34:56', TIME '01:02:03')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('MINUTE', time'12:34:56', time'01:02:03')
+-- !query analysis
+Project [time_diff(MINUTE, 12:34:56, 01:02:03) AS time_diff(MINUTE, TIME '12:34:56', TIME '01:02:03')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('SECOND', time'12:34:56', time'01:02:03')
+-- !query analysis
+Project [time_diff(SECOND, 12:34:56, 01:02:03) AS time_diff(SECOND, TIME '12:34:56', TIME '01:02:03')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('HOUR', time'00:00:00', time'12:34:56.1')
+-- !query analysis
+Project [time_diff(HOUR, 00:00:00, 12:34:56.1) AS time_diff(HOUR, TIME '00:00:00', TIME '12:34:56.1')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('MINUTE', time'00:00:00', time'12:34:56.1')
+-- !query analysis
+Project [time_diff(MINUTE, 00:00:00, 12:34:56.1) AS time_diff(MINUTE, TIME '00:00:00', TIME '12:34:56.1')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('SECOND', time'00:00:00', time'12:34:56.1')
+-- !query analysis
+Project [time_diff(SECOND, 00:00:00, 12:34:56.1) AS time_diff(SECOND, TIME '00:00:00', TIME '12:34:56.1')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('MILLISECOND', time'00:00:00', time'12:34:56.1')
+-- !query analysis
+Project [time_diff(MILLISECOND, 00:00:00, 12:34:56.1) AS time_diff(MILLISECOND, TIME '00:00:00', TIME '12:34:56.1')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('MICROSECOND', time'00:00:00', time'12:34:56.1')
+-- !query analysis
+Project [time_diff(MICROSECOND, 00:00:00, 12:34:56.1) AS time_diff(MICROSECOND, TIME '00:00:00', TIME '12:34:56.1')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('HOUR', time'00:00:00', time'12:34:56.123456')
+-- !query analysis
+Project [time_diff(HOUR, 00:00:00, 12:34:56.123456) AS time_diff(HOUR, TIME '00:00:00', TIME '12:34:56.123456')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('MINUTE', time'00:00:00', time'12:34:56.123456')
+-- !query analysis
+Project [time_diff(MINUTE, 00:00:00, 12:34:56.123456) AS time_diff(MINUTE, TIME '00:00:00', TIME '12:34:56.123456')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('SECOND', time'00:00:00', time'12:34:56.123456')
+-- !query analysis
+Project [time_diff(SECOND, 00:00:00, 12:34:56.123456) AS time_diff(SECOND, TIME '00:00:00', TIME '12:34:56.123456')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('MILLISECOND', time'00:00:00', time'12:34:56.123456')
+-- !query analysis
+Project [time_diff(MILLISECOND, 00:00:00, 12:34:56.123456) AS time_diff(MILLISECOND, TIME '00:00:00', TIME '12:34:56.123456')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('MICROSECOND', time'00:00:00', time'12:34:56.123456')
+-- !query analysis
+Project [time_diff(MICROSECOND, 00:00:00, 12:34:56.123456) AS time_diff(MICROSECOND, TIME '00:00:00', TIME '12:34:56.123456')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('HOUR', time'00:00:00', time'12:34:56.123456789')
+-- !query analysis
+Project [time_diff(HOUR, 00:00:00, 12:34:56.123456) AS time_diff(HOUR, TIME '00:00:00', TIME '12:34:56.123456')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('MINUTE', time'00:00:00', time'12:34:56.123456789')
+-- !query analysis
+Project [time_diff(MINUTE, 00:00:00, 12:34:56.123456) AS time_diff(MINUTE, TIME '00:00:00', TIME '12:34:56.123456')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('SECOND', time'00:00:00', time'12:34:56.123456789')
+-- !query analysis
+Project [time_diff(SECOND, 00:00:00, 12:34:56.123456) AS time_diff(SECOND, TIME '00:00:00', TIME '12:34:56.123456')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('MILLISECOND', time'00:00:00', time'12:34:56.123456789')
+-- !query analysis
+Project [time_diff(MILLISECOND, 00:00:00, 12:34:56.123456) AS time_diff(MILLISECOND, TIME '00:00:00', TIME '12:34:56.123456')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('MICROSECOND', time'00:00:00', time'12:34:56.123456789')
+-- !query analysis
+Project [time_diff(MICROSECOND, 00:00:00, 12:34:56.123456) AS time_diff(MICROSECOND, TIME '00:00:00', TIME '12:34:56.123456')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('hour', time'00:00:00', time'12:34:56')
+-- !query analysis
+Project [time_diff(hour, 00:00:00, 12:34:56) AS time_diff(hour, TIME '00:00:00', TIME '12:34:56')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('MiNuTe', time'00:00:00', time'12:34:56')
+-- !query analysis
+Project [time_diff(MiNuTe, 00:00:00, 12:34:56) AS time_diff(MiNuTe, TIME '00:00:00', TIME '12:34:56')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('sEcOnD', time'00:00:00', time'12:34:56')
+-- !query analysis
+Project [time_diff(sEcOnD, 00:00:00, 12:34:56) AS time_diff(sEcOnD, TIME '00:00:00', TIME '12:34:56')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('Millisecond', time'00:00:00', time'12:34:56')
+-- !query analysis
+Project [time_diff(Millisecond, 00:00:00, 12:34:56) AS time_diff(Millisecond, TIME '00:00:00', TIME '12:34:56')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('microseconD', time'00:00:00', time'12:34:56')
+-- !query analysis
+Project [time_diff(microseconD, 00:00:00, 12:34:56) AS time_diff(microseconD, TIME '00:00:00', TIME '12:34:56')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('HOUR', time'00:00:00', time'00:00:00')
+-- !query analysis
+Project [time_diff(HOUR, 00:00:00, 00:00:00) AS time_diff(HOUR, TIME '00:00:00', TIME '00:00:00')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('MINUTE', time'00:00:00', time'00:00:00')
+-- !query analysis
+Project [time_diff(MINUTE, 00:00:00, 00:00:00) AS time_diff(MINUTE, TIME '00:00:00', TIME '00:00:00')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('SECOND', time'00:00:00', time'00:00:00')
+-- !query analysis
+Project [time_diff(SECOND, 00:00:00, 00:00:00) AS time_diff(SECOND, TIME '00:00:00', TIME '00:00:00')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('MILLISECOND', time'00:00:00', time'00:00:00')
+-- !query analysis
+Project [time_diff(MILLISECOND, 00:00:00, 00:00:00) AS time_diff(MILLISECOND, TIME '00:00:00', TIME '00:00:00')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('MICROSECOND', time'00:00:00', time'00:00:00')
+-- !query analysis
+Project [time_diff(MICROSECOND, 00:00:00, 00:00:00) AS time_diff(MICROSECOND, TIME '00:00:00', TIME '00:00:00')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('HOUR', time'00:00:00', time'00:00:00.000000001')
+-- !query analysis
+Project [time_diff(HOUR, 00:00:00, 00:00:00) AS time_diff(HOUR, TIME '00:00:00', TIME '00:00:00')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('MINUTE', time'00:00:00', time'00:00:00.000000001')
+-- !query analysis
+Project [time_diff(MINUTE, 00:00:00, 00:00:00) AS time_diff(MINUTE, TIME '00:00:00', TIME '00:00:00')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('SECOND', time'00:00:00', time'00:00:00.000000001')
+-- !query analysis
+Project [time_diff(SECOND, 00:00:00, 00:00:00) AS time_diff(SECOND, TIME '00:00:00', TIME '00:00:00')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('MILLISECOND', time'00:00:00', time'00:00:00.000000001')
+-- !query analysis
+Project [time_diff(MILLISECOND, 00:00:00, 00:00:00) AS time_diff(MILLISECOND, TIME '00:00:00', TIME '00:00:00')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('MICROSECOND', time'00:00:00', time'00:00:00.000000001')
+-- !query analysis
+Project [time_diff(MICROSECOND, 00:00:00, 00:00:00) AS time_diff(MICROSECOND, TIME '00:00:00', TIME '00:00:00')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('HOUR', time'00:00:00', time'23:59:59.999999999')
+-- !query analysis
+Project [time_diff(HOUR, 00:00:00, 23:59:59.999999) AS time_diff(HOUR, TIME '00:00:00', TIME '23:59:59.999999')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('MINUTE', time'00:00:00', time'23:59:59.999999999')
+-- !query analysis
+Project [time_diff(MINUTE, 00:00:00, 23:59:59.999999) AS time_diff(MINUTE, TIME '00:00:00', TIME '23:59:59.999999')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('SECOND', time'00:00:00', time'23:59:59.999999999')
+-- !query analysis
+Project [time_diff(SECOND, 00:00:00, 23:59:59.999999) AS time_diff(SECOND, TIME '00:00:00', TIME '23:59:59.999999')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('MILLISECOND', time'00:00:00', time'23:59:59.999999999')
+-- !query analysis
+Project [time_diff(MILLISECOND, 00:00:00, 23:59:59.999999) AS time_diff(MILLISECOND, TIME '00:00:00', TIME '23:59:59.999999')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('MICROSECOND', time'00:00:00', time'23:59:59.999999999')
+-- !query analysis
+Project [time_diff(MICROSECOND, 00:00:00, 23:59:59.999999) AS time_diff(MICROSECOND, TIME '00:00:00', TIME '23:59:59.999999')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('', time'00:00:00', time'12:34:56')
+-- !query analysis
+Project [time_diff(, 00:00:00, 12:34:56) AS time_diff(, TIME '00:00:00', TIME '12:34:56')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff(' ', time'00:00:00', time'12:34:56')
+-- !query analysis
+Project [time_diff( , 00:00:00, 12:34:56) AS time_diff( , TIME '00:00:00', TIME '12:34:56')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('MS', time'00:00:00', time'12:34:56')
+-- !query analysis
+Project [time_diff(MS, 00:00:00, 12:34:56) AS time_diff(MS, TIME '00:00:00', TIME '12:34:56')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('DAY', time'00:00:00', time'12:34:56')
+-- !query analysis
+Project [time_diff(DAY, 00:00:00, 12:34:56) AS time_diff(DAY, TIME '00:00:00', TIME '12:34:56')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('WEEK', time'00:00:00', time'12:34:56')
+-- !query analysis
+Project [time_diff(WEEK, 00:00:00, 12:34:56) AS time_diff(WEEK, TIME '00:00:00', TIME '12:34:56')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('ABCD', time'00:00:00', time'12:34:56')
+-- !query analysis
+Project [time_diff(ABCD, 00:00:00, 12:34:56) AS time_diff(ABCD, TIME '00:00:00', TIME '12:34:56')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('QUARTER', time'00:00:00', time'12:34:56')
+-- !query analysis
+Project [time_diff(QUARTER, 00:00:00, 12:34:56) AS time_diff(QUARTER, TIME '00:00:00', TIME '12:34:56')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('INVALID', time'00:00:00', time'12:34:56')
+-- !query analysis
+Project [time_diff(INVALID, 00:00:00, 12:34:56) AS time_diff(INVALID, TIME '00:00:00', TIME '12:34:56')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('INVALID_UNIT', time'00:00:00', time'12:34:56')
+-- !query analysis
+Project [time_diff(INVALID_UNIT, 00:00:00, 12:34:56) AS time_diff(INVALID_UNIT, TIME '00:00:00', TIME '12:34:56')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff(NULL, time'00:00:00', time'12:34:56')
+-- !query analysis
+Project [time_diff(cast(null as string), 00:00:00, 12:34:56) AS time_diff(NULL, TIME '00:00:00', TIME '12:34:56')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('MICROSECOND', NULL, time'12:34:56')
+-- !query analysis
+Project [time_diff(MICROSECOND, cast(null as time(6)), 12:34:56) AS time_diff(MICROSECOND, NULL, TIME '12:34:56')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('MICROSECOND', time'00:00:00', NULL)
+-- !query analysis
+Project [time_diff(MICROSECOND, 00:00:00, cast(null as time(6))) AS time_diff(MICROSECOND, TIME '00:00:00', NULL)#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff(NULL, NULL, time'12:34:56')
+-- !query analysis
+Project [time_diff(cast(null as string), cast(null as time(6)), 12:34:56) AS time_diff(NULL, NULL, TIME '12:34:56')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff(NULL, time'00:00:00', NULL)
+-- !query analysis
+Project [time_diff(cast(null as string), 00:00:00, cast(null as time(6))) AS time_diff(NULL, TIME '00:00:00', NULL)#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('MICROSECOND', NULL, NULL)
+-- !query analysis
+Project [time_diff(MICROSECOND, cast(null as time(6)), cast(null as time(6))) AS time_diff(MICROSECOND, NULL, NULL)#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff(NULL, NULL, NULL)
+-- !query analysis
+Project [time_diff(cast(null as string), cast(null as time(6)), cast(null as time(6))) AS time_diff(NULL, NULL, NULL)#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT time_diff('SECOND', time_start, time_end) FROM timediff_view
+-- !query analysis
+Project [time_diff(SECOND, time_start#x, time_end#x) AS time_diff(SECOND, time_start, time_end)#xL]
++- SubqueryAlias timediff_view
+ +- View (`timediff_view`, [time_start#x, time_end#x, unit#x])
+ +- Project [cast(time_start#x as time(6)) AS time_start#x, cast(time_end#x as time(6)) AS time_end#x, cast(unit#x as string) AS unit#x]
+ +- Project [01:02:03 AS time_start#x, 04:05:06 AS time_end#x, SECOND AS unit#x]
+ +- OneRowRelation
+
+
+-- !query
+SELECT time_diff(unit, time'01:02:03', time_end) FROM timediff_view
+-- !query analysis
+Project [time_diff(unit#x, 01:02:03, time_end#x) AS time_diff(unit, TIME '01:02:03', time_end)#xL]
++- SubqueryAlias timediff_view
+ +- View (`timediff_view`, [time_start#x, time_end#x, unit#x])
+ +- Project [cast(time_start#x as time(6)) AS time_start#x, cast(time_end#x as time(6)) AS time_end#x, cast(unit#x as string) AS unit#x]
+ +- Project [01:02:03 AS time_start#x, 04:05:06 AS time_end#x, SECOND AS unit#x]
+ +- OneRowRelation
+
+
+-- !query
+SELECT time_diff(unit, time_start, time'04:05:06') FROM timediff_view
+-- !query analysis
+Project [time_diff(unit#x, time_start#x, 04:05:06) AS time_diff(unit, time_start, TIME '04:05:06')#xL]
++- SubqueryAlias timediff_view
+ +- View (`timediff_view`, [time_start#x, time_end#x, unit#x])
+ +- Project [cast(time_start#x as time(6)) AS time_start#x, cast(time_end#x as time(6)) AS time_end#x, cast(unit#x as string) AS unit#x]
+ +- Project [01:02:03 AS time_start#x, 04:05:06 AS time_end#x, SECOND AS unit#x]
+ +- OneRowRelation
+
+
+-- !query
+SELECT time_diff('SECOND', time'01:02:03', time_end) FROM timediff_view
+-- !query analysis
+Project [time_diff(SECOND, 01:02:03, time_end#x) AS time_diff(SECOND, TIME '01:02:03', time_end)#xL]
++- SubqueryAlias timediff_view
+ +- View (`timediff_view`, [time_start#x, time_end#x, unit#x])
+ +- Project [cast(time_start#x as time(6)) AS time_start#x, cast(time_end#x as time(6)) AS time_end#x, cast(unit#x as string) AS unit#x]
+ +- Project [01:02:03 AS time_start#x, 04:05:06 AS time_end#x, SECOND AS unit#x]
+ +- OneRowRelation
+
+
+-- !query
+SELECT time_diff('SECOND', time_start, time'04:05:06') FROM timediff_view
+-- !query analysis
+Project [time_diff(SECOND, time_start#x, 04:05:06) AS time_diff(SECOND, time_start, TIME '04:05:06')#xL]
++- SubqueryAlias timediff_view
+ +- View (`timediff_view`, [time_start#x, time_end#x, unit#x])
+ +- Project [cast(time_start#x as time(6)) AS time_start#x, cast(time_end#x as time(6)) AS time_end#x, cast(unit#x as string) AS unit#x]
+ +- Project [01:02:03 AS time_start#x, 04:05:06 AS time_end#x, SECOND AS unit#x]
+ +- OneRowRelation
+
+
+-- !query
+SELECT time_diff(unit, time'01:02:03', time'04:05:06') FROM timediff_view
+-- !query analysis
+Project [time_diff(unit#x, 01:02:03, 04:05:06) AS time_diff(unit, TIME '01:02:03', TIME '04:05:06')#xL]
++- SubqueryAlias timediff_view
+ +- View (`timediff_view`, [time_start#x, time_end#x, unit#x])
+ +- Project [cast(time_start#x as time(6)) AS time_start#x, cast(time_end#x as time(6)) AS time_end#x, cast(unit#x as string) AS unit#x]
+ +- Project [01:02:03 AS time_start#x, 04:05:06 AS time_end#x, SECOND AS unit#x]
+ +- OneRowRelation
+
+
+-- !query
+SELECT time_diff(unit, time_start, time_end) FROM timediff_view
+-- !query analysis
+Project [time_diff(unit#x, time_start#x, time_end#x) AS time_diff(unit, time_start, time_end)#xL]
++- SubqueryAlias timediff_view
+ +- View (`timediff_view`, [time_start#x, time_end#x, unit#x])
+ +- Project [cast(time_start#x as time(6)) AS time_start#x, cast(time_end#x as time(6)) AS time_end#x, cast(unit#x as string) AS unit#x]
+ +- Project [01:02:03 AS time_start#x, 04:05:06 AS time_end#x, SECOND AS unit#x]
+ +- OneRowRelation
+
+
-- !query
SELECT TIME'12:30:41' - TIME'10:00'
-- !query analysis
diff --git a/sql/core/src/test/resources/sql-tests/inputs/time.sql b/sql/core/src/test/resources/sql-tests/inputs/time.sql
index 44d44e2b4a42..0c9eda1eb4af 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/time.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/time.sql
@@ -1,5 +1,7 @@
-- time literals, functions and operations
+create temporary view timediff_view as select time'01:02:03' time_start, time'04:05:06' time_end, 'SECOND' unit;
+
create temporary view time_view as select '11:53:26.038344' time_str, 'HH:mm:ss.SSSSSS' fmt_str;
create temporary view trunc_time_view as select time'11:53:26.038344' time_val, 'MINUTE' unit;
@@ -171,6 +173,93 @@ SELECT '00:00:00.0001' :: TIME(4) - INTERVAL '0 00:00:00.0001' DAY TO SECOND;
SELECT '08:30' :: TIME(0) - INTERVAL '6' HOUR;
SELECT '10:00:01' :: TIME(1) - INTERVAL '1' MONTH;
+-- SPARK-51555: time difference.
+SELECT time_diff('HOUR', time'00:00:00', time'12:34:56');
+SELECT time_diff('MINUTE', time'00:00:00', time'12:34:56');
+SELECT time_diff('SECOND', time'00:00:00', time'12:34:56');
+SELECT time_diff('MILLISECOND', time'00:00:00', time'12:34:56');
+SELECT time_diff('MICROSECOND', time'00:00:00', time'12:34:56');
+
+-- SPARK-51555: positive and negative time difference.
+SELECT time_diff('HOUR', time'01:02:03', time'12:34:56');
+SELECT time_diff('MINUTE', time'01:02:03', time'12:34:56');
+SELECT time_diff('SECOND', time'01:02:03', time'12:34:56');
+SELECT time_diff('HOUR', time'12:34:56', time'01:02:03');
+SELECT time_diff('MINUTE', time'12:34:56', time'01:02:03');
+SELECT time_diff('SECOND', time'12:34:56', time'01:02:03');
+
+-- SPARK-51555: time difference with various time precisions.
+SELECT time_diff('HOUR', time'00:00:00', time'12:34:56.1');
+SELECT time_diff('MINUTE', time'00:00:00', time'12:34:56.1');
+SELECT time_diff('SECOND', time'00:00:00', time'12:34:56.1');
+SELECT time_diff('MILLISECOND', time'00:00:00', time'12:34:56.1');
+SELECT time_diff('MICROSECOND', time'00:00:00', time'12:34:56.1');
+SELECT time_diff('HOUR', time'00:00:00', time'12:34:56.123456');
+SELECT time_diff('MINUTE', time'00:00:00', time'12:34:56.123456');
+SELECT time_diff('SECOND', time'00:00:00', time'12:34:56.123456');
+SELECT time_diff('MILLISECOND', time'00:00:00', time'12:34:56.123456');
+SELECT time_diff('MICROSECOND', time'00:00:00', time'12:34:56.123456');
+SELECT time_diff('HOUR', time'00:00:00', time'12:34:56.123456789');
+SELECT time_diff('MINUTE', time'00:00:00', time'12:34:56.123456789');
+SELECT time_diff('SECOND', time'00:00:00', time'12:34:56.123456789');
+SELECT time_diff('MILLISECOND', time'00:00:00', time'12:34:56.123456789');
+SELECT time_diff('MICROSECOND', time'00:00:00', time'12:34:56.123456789');
+
+-- SPARK-51555: time difference with various unit cases.
+SELECT time_diff('hour', time'00:00:00', time'12:34:56');
+SELECT time_diff('MiNuTe', time'00:00:00', time'12:34:56');
+SELECT time_diff('sEcOnD', time'00:00:00', time'12:34:56');
+SELECT time_diff('Millisecond', time'00:00:00', time'12:34:56');
+SELECT time_diff('microseconD', time'00:00:00', time'12:34:56');
+
+-- SPARK-51555: time difference with zero time.
+SELECT time_diff('HOUR', time'00:00:00', time'00:00:00');
+SELECT time_diff('MINUTE', time'00:00:00', time'00:00:00');
+SELECT time_diff('SECOND', time'00:00:00', time'00:00:00');
+SELECT time_diff('MILLISECOND', time'00:00:00', time'00:00:00');
+SELECT time_diff('MICROSECOND', time'00:00:00', time'00:00:00');
+-- SPARK-51555: time difference with small time.
+SELECT time_diff('HOUR', time'00:00:00', time'00:00:00.000000001');
+SELECT time_diff('MINUTE', time'00:00:00', time'00:00:00.000000001');
+SELECT time_diff('SECOND', time'00:00:00', time'00:00:00.000000001');
+SELECT time_diff('MILLISECOND', time'00:00:00', time'00:00:00.000000001');
+SELECT time_diff('MICROSECOND', time'00:00:00', time'00:00:00.000000001');
+-- SPARK-51555: time difference with max time.
+SELECT time_diff('HOUR', time'00:00:00', time'23:59:59.999999999');
+SELECT time_diff('MINUTE', time'00:00:00', time'23:59:59.999999999');
+SELECT time_diff('SECOND', time'00:00:00', time'23:59:59.999999999');
+SELECT time_diff('MILLISECOND', time'00:00:00', time'23:59:59.999999999');
+SELECT time_diff('MICROSECOND', time'00:00:00', time'23:59:59.999999999');
+
+-- SPARK-51555: time difference with invalid unit.
+SELECT time_diff('', time'00:00:00', time'12:34:56');
+SELECT time_diff(' ', time'00:00:00', time'12:34:56');
+SELECT time_diff('MS', time'00:00:00', time'12:34:56');
+SELECT time_diff('DAY', time'00:00:00', time'12:34:56');
+SELECT time_diff('WEEK', time'00:00:00', time'12:34:56');
+SELECT time_diff('ABCD', time'00:00:00', time'12:34:56');
+SELECT time_diff('QUARTER', time'00:00:00', time'12:34:56');
+SELECT time_diff('INVALID', time'00:00:00', time'12:34:56');
+SELECT time_diff('INVALID_UNIT', time'00:00:00', time'12:34:56');
+
+-- SPARK-51555: time difference with null inputs.
+SELECT time_diff(NULL, time'00:00:00', time'12:34:56');
+SELECT time_diff('MICROSECOND', NULL, time'12:34:56');
+SELECT time_diff('MICROSECOND', time'00:00:00', NULL);
+SELECT time_diff(NULL, NULL, time'12:34:56');
+SELECT time_diff(NULL, time'00:00:00', NULL);
+SELECT time_diff('MICROSECOND', NULL, NULL);
+SELECT time_diff(NULL, NULL, NULL);
+
+-- SPARK-51555: time difference with table columns.
+SELECT time_diff('SECOND', time_start, time_end) FROM timediff_view;
+SELECT time_diff(unit, time'01:02:03', time_end) FROM timediff_view;
+SELECT time_diff(unit, time_start, time'04:05:06') FROM timediff_view;
+SELECT time_diff('SECOND', time'01:02:03', time_end) FROM timediff_view;
+SELECT time_diff('SECOND', time_start, time'04:05:06') FROM timediff_view;
+SELECT time_diff(unit, time'01:02:03', time'04:05:06') FROM timediff_view;
+SELECT time_diff(unit, time_start, time_end) FROM timediff_view;
+
-- Subtract times
SELECT TIME'12:30:41' - TIME'10:00';
SELECT TIME'08:30' - NULL;
diff --git a/sql/core/src/test/resources/sql-tests/results/time.sql.out b/sql/core/src/test/resources/sql-tests/results/time.sql.out
index bad832c0c726..27e869d9e5c7 100644
--- a/sql/core/src/test/resources/sql-tests/results/time.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/time.sql.out
@@ -1,4 +1,12 @@
-- Automatically generated by SQLQueryTestSuite
+-- !query
+create temporary view timediff_view as select time'01:02:03' time_start, time'04:05:06' time_end, 'SECOND' unit
+-- !query schema
+struct<>
+-- !query output
+
+
+
-- !query
create temporary view time_view as select '11:53:26.038344' time_str, 'HH:mm:ss.SSSSSS' fmt_str
-- !query schema
@@ -891,7 +899,7 @@ struct<>
-- !query output
org.apache.spark.SparkIllegalArgumentException
{
- "errorClass" : "INVALID_PARAMETER_VALUE.TIMETRUNC_UNIT",
+ "errorClass" : "INVALID_PARAMETER_VALUE.TIME_UNIT",
"sqlState" : "22023",
"messageParameters" : {
"functionName" : "`time_trunc`",
@@ -908,7 +916,7 @@ struct<>
-- !query output
org.apache.spark.SparkIllegalArgumentException
{
- "errorClass" : "INVALID_PARAMETER_VALUE.TIMETRUNC_UNIT",
+ "errorClass" : "INVALID_PARAMETER_VALUE.TIME_UNIT",
"sqlState" : "22023",
"messageParameters" : {
"functionName" : "`time_trunc`",
@@ -925,7 +933,7 @@ struct<>
-- !query output
org.apache.spark.SparkIllegalArgumentException
{
- "errorClass" : "INVALID_PARAMETER_VALUE.TIMETRUNC_UNIT",
+ "errorClass" : "INVALID_PARAMETER_VALUE.TIME_UNIT",
"sqlState" : "22023",
"messageParameters" : {
"functionName" : "`time_trunc`",
@@ -942,7 +950,7 @@ struct<>
-- !query output
org.apache.spark.SparkIllegalArgumentException
{
- "errorClass" : "INVALID_PARAMETER_VALUE.TIMETRUNC_UNIT",
+ "errorClass" : "INVALID_PARAMETER_VALUE.TIME_UNIT",
"sqlState" : "22023",
"messageParameters" : {
"functionName" : "`time_trunc`",
@@ -959,7 +967,7 @@ struct<>
-- !query output
org.apache.spark.SparkIllegalArgumentException
{
- "errorClass" : "INVALID_PARAMETER_VALUE.TIMETRUNC_UNIT",
+ "errorClass" : "INVALID_PARAMETER_VALUE.TIME_UNIT",
"sqlState" : "22023",
"messageParameters" : {
"functionName" : "`time_trunc`",
@@ -976,7 +984,7 @@ struct<>
-- !query output
org.apache.spark.SparkIllegalArgumentException
{
- "errorClass" : "INVALID_PARAMETER_VALUE.TIMETRUNC_UNIT",
+ "errorClass" : "INVALID_PARAMETER_VALUE.TIME_UNIT",
"sqlState" : "22023",
"messageParameters" : {
"functionName" : "`time_trunc`",
@@ -993,7 +1001,7 @@ struct<>
-- !query output
org.apache.spark.SparkIllegalArgumentException
{
- "errorClass" : "INVALID_PARAMETER_VALUE.TIMETRUNC_UNIT",
+ "errorClass" : "INVALID_PARAMETER_VALUE.TIME_UNIT",
"sqlState" : "22023",
"messageParameters" : {
"functionName" : "`time_trunc`",
@@ -1010,7 +1018,7 @@ struct<>
-- !query output
org.apache.spark.SparkIllegalArgumentException
{
- "errorClass" : "INVALID_PARAMETER_VALUE.TIMETRUNC_UNIT",
+ "errorClass" : "INVALID_PARAMETER_VALUE.TIME_UNIT",
"sqlState" : "22023",
"messageParameters" : {
"functionName" : "`time_trunc`",
@@ -1027,7 +1035,7 @@ struct<>
-- !query output
org.apache.spark.SparkIllegalArgumentException
{
- "errorClass" : "INVALID_PARAMETER_VALUE.TIMETRUNC_UNIT",
+ "errorClass" : "INVALID_PARAMETER_VALUE.TIME_UNIT",
"sqlState" : "22023",
"messageParameters" : {
"functionName" : "`time_trunc`",
@@ -1237,6 +1245,639 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
}
+-- !query
+SELECT time_diff('HOUR', time'00:00:00', time'12:34:56')
+-- !query schema
+struct<time_diff(HOUR, TIME '00:00:00', TIME '12:34:56'):bigint>
+-- !query output
+12
+
+
+-- !query
+SELECT time_diff('MINUTE', time'00:00:00', time'12:34:56')
+-- !query schema
+struct<time_diff(MINUTE, TIME '00:00:00', TIME '12:34:56'):bigint>
+-- !query output
+754
+
+
+-- !query
+SELECT time_diff('SECOND', time'00:00:00', time'12:34:56')
+-- !query schema
+struct<time_diff(SECOND, TIME '00:00:00', TIME '12:34:56'):bigint>
+-- !query output
+45296
+
+
+-- !query
+SELECT time_diff('MILLISECOND', time'00:00:00', time'12:34:56')
+-- !query schema
+struct<time_diff(MILLISECOND, TIME '00:00:00', TIME '12:34:56'):bigint>
+-- !query output
+45296000
+
+
+-- !query
+SELECT time_diff('MICROSECOND', time'00:00:00', time'12:34:56')
+-- !query schema
+struct<time_diff(MICROSECOND, TIME '00:00:00', TIME '12:34:56'):bigint>
+-- !query output
+45296000000
+
+
+-- !query
+SELECT time_diff('HOUR', time'01:02:03', time'12:34:56')
+-- !query schema
+struct<time_diff(HOUR, TIME '01:02:03', TIME '12:34:56'):bigint>
+-- !query output
+11
+
+
+-- !query
+SELECT time_diff('MINUTE', time'01:02:03', time'12:34:56')
+-- !query schema
+struct<time_diff(MINUTE, TIME '01:02:03', TIME '12:34:56'):bigint>
+-- !query output
+692
+
+
+-- !query
+SELECT time_diff('SECOND', time'01:02:03', time'12:34:56')
+-- !query schema
+struct<time_diff(SECOND, TIME '01:02:03', TIME '12:34:56'):bigint>
+-- !query output
+41573
+
+
+-- !query
+SELECT time_diff('HOUR', time'12:34:56', time'01:02:03')
+-- !query schema
+struct<time_diff(HOUR, TIME '12:34:56', TIME '01:02:03'):bigint>
+-- !query output
+-11
+
+
+-- !query
+SELECT time_diff('MINUTE', time'12:34:56', time'01:02:03')
+-- !query schema
+struct<time_diff(MINUTE, TIME '12:34:56', TIME '01:02:03'):bigint>
+-- !query output
+-692
+
+
+-- !query
+SELECT time_diff('SECOND', time'12:34:56', time'01:02:03')
+-- !query schema
+struct<time_diff(SECOND, TIME '12:34:56', TIME '01:02:03'):bigint>
+-- !query output
+-41573
+
+
+-- !query
+SELECT time_diff('HOUR', time'00:00:00', time'12:34:56.1')
+-- !query schema
+struct<time_diff(HOUR, TIME '00:00:00', TIME '12:34:56.1'):bigint>
+-- !query output
+12
+
+
+-- !query
+SELECT time_diff('MINUTE', time'00:00:00', time'12:34:56.1')
+-- !query schema
+struct<time_diff(MINUTE, TIME '00:00:00', TIME '12:34:56.1'):bigint>
+-- !query output
+754
+
+
+-- !query
+SELECT time_diff('SECOND', time'00:00:00', time'12:34:56.1')
+-- !query schema
+struct<time_diff(SECOND, TIME '00:00:00', TIME '12:34:56.1'):bigint>
+-- !query output
+45296
+
+
+-- !query
+SELECT time_diff('MILLISECOND', time'00:00:00', time'12:34:56.1')
+-- !query schema
+struct<time_diff(MILLISECOND, TIME '00:00:00', TIME '12:34:56.1'):bigint>
+-- !query output
+45296100
+
+
+-- !query
+SELECT time_diff('MICROSECOND', time'00:00:00', time'12:34:56.1')
+-- !query schema
+struct<time_diff(MICROSECOND, TIME '00:00:00', TIME '12:34:56.1'):bigint>
+-- !query output
+45296100000
+
+
+-- !query
+SELECT time_diff('HOUR', time'00:00:00', time'12:34:56.123456')
+-- !query schema
+struct<time_diff(HOUR, TIME '00:00:00', TIME '12:34:56.123456'):bigint>
+-- !query output
+12
+
+
+-- !query
+SELECT time_diff('MINUTE', time'00:00:00', time'12:34:56.123456')
+-- !query schema
+struct<time_diff(MINUTE, TIME '00:00:00', TIME '12:34:56.123456'):bigint>
+-- !query output
+754
+
+
+-- !query
+SELECT time_diff('SECOND', time'00:00:00', time'12:34:56.123456')
+-- !query schema
+struct<time_diff(SECOND, TIME '00:00:00', TIME '12:34:56.123456'):bigint>
+-- !query output
+45296
+
+
+-- !query
+SELECT time_diff('MILLISECOND', time'00:00:00', time'12:34:56.123456')
+-- !query schema
+struct<time_diff(MILLISECOND, TIME '00:00:00', TIME '12:34:56.123456'):bigint>
+-- !query output
+45296123
+
+
+-- !query
+SELECT time_diff('MICROSECOND', time'00:00:00', time'12:34:56.123456')
+-- !query schema
+struct<time_diff(MICROSECOND, TIME '00:00:00', TIME '12:34:56.123456'):bigint>
+-- !query output
+45296123456
+
+
+-- !query
+SELECT time_diff('HOUR', time'00:00:00', time'12:34:56.123456789')
+-- !query schema
+struct<time_diff(HOUR, TIME '00:00:00', TIME '12:34:56.123456'):bigint>
+-- !query output
+12
+
+
+-- !query
+SELECT time_diff('MINUTE', time'00:00:00', time'12:34:56.123456789')
+-- !query schema
+struct<time_diff(MINUTE, TIME '00:00:00', TIME '12:34:56.123456'):bigint>
+-- !query output
+754
+
+
+-- !query
+SELECT time_diff('SECOND', time'00:00:00', time'12:34:56.123456789')
+-- !query schema
+struct<time_diff(SECOND, TIME '00:00:00', TIME '12:34:56.123456'):bigint>
+-- !query output
+45296
+
+
+-- !query
+SELECT time_diff('MILLISECOND', time'00:00:00', time'12:34:56.123456789')
+-- !query schema
+struct<time_diff(MILLISECOND, TIME '00:00:00', TIME '12:34:56.123456'):bigint>
+-- !query output
+45296123
+
+
+-- !query
+SELECT time_diff('MICROSECOND', time'00:00:00', time'12:34:56.123456789')
+-- !query schema
+struct<time_diff(MICROSECOND, TIME '00:00:00', TIME '12:34:56.123456'):bigint>
+-- !query output
+45296123456
+
+
+-- !query
+SELECT time_diff('hour', time'00:00:00', time'12:34:56')
+-- !query schema
+struct<time_diff(hour, TIME '00:00:00', TIME '12:34:56'):bigint>
+-- !query output
+12
+
+
+-- !query
+SELECT time_diff('MiNuTe', time'00:00:00', time'12:34:56')
+-- !query schema
+struct<time_diff(MiNuTe, TIME '00:00:00', TIME '12:34:56'):bigint>
+-- !query output
+754
+
+
+-- !query
+SELECT time_diff('sEcOnD', time'00:00:00', time'12:34:56')
+-- !query schema
+struct<time_diff(sEcOnD, TIME '00:00:00', TIME '12:34:56'):bigint>
+-- !query output
+45296
+
+
+-- !query
+SELECT time_diff('Millisecond', time'00:00:00', time'12:34:56')
+-- !query schema
+struct<time_diff(Millisecond, TIME '00:00:00', TIME '12:34:56'):bigint>
+-- !query output
+45296000
+
+
+-- !query
+SELECT time_diff('microseconD', time'00:00:00', time'12:34:56')
+-- !query schema
+struct<time_diff(microseconD, TIME '00:00:00', TIME '12:34:56'):bigint>
+-- !query output
+45296000000
+
+
+-- !query
+SELECT time_diff('HOUR', time'00:00:00', time'00:00:00')
+-- !query schema
+struct<time_diff(HOUR, TIME '00:00:00', TIME '00:00:00'):bigint>
+-- !query output
+0
+
+
+-- !query
+SELECT time_diff('MINUTE', time'00:00:00', time'00:00:00')
+-- !query schema
+struct<time_diff(MINUTE, TIME '00:00:00', TIME '00:00:00'):bigint>
+-- !query output
+0
+
+
+-- !query
+SELECT time_diff('SECOND', time'00:00:00', time'00:00:00')
+-- !query schema
+struct<time_diff(SECOND, TIME '00:00:00', TIME '00:00:00'):bigint>
+-- !query output
+0
+
+
+-- !query
+SELECT time_diff('MILLISECOND', time'00:00:00', time'00:00:00')
+-- !query schema
+struct<time_diff(MILLISECOND, TIME '00:00:00', TIME '00:00:00'):bigint>
+-- !query output
+0
+
+
+-- !query
+SELECT time_diff('MICROSECOND', time'00:00:00', time'00:00:00')
+-- !query schema
+struct<time_diff(MICROSECOND, TIME '00:00:00', TIME '00:00:00'):bigint>
+-- !query output
+0
+
+
+-- !query
+SELECT time_diff('HOUR', time'00:00:00', time'00:00:00.000000001')
+-- !query schema
+struct<time_diff(HOUR, TIME '00:00:00', TIME '00:00:00'):bigint>
+-- !query output
+0
+
+
+-- !query
+SELECT time_diff('MINUTE', time'00:00:00', time'00:00:00.000000001')
+-- !query schema
+struct<time_diff(MINUTE, TIME '00:00:00', TIME '00:00:00'):bigint>
+-- !query output
+0
+
+
+-- !query
+SELECT time_diff('SECOND', time'00:00:00', time'00:00:00.000000001')
+-- !query schema
+struct<time_diff(SECOND, TIME '00:00:00', TIME '00:00:00'):bigint>
+-- !query output
+0
+
+
+-- !query
+SELECT time_diff('MILLISECOND', time'00:00:00', time'00:00:00.000000001')
+-- !query schema
+struct<time_diff(MILLISECOND, TIME '00:00:00', TIME '00:00:00'):bigint>
+-- !query output
+0
+
+
+-- !query
+SELECT time_diff('MICROSECOND', time'00:00:00', time'00:00:00.000000001')
+-- !query schema
+struct<time_diff(MICROSECOND, TIME '00:00:00', TIME '00:00:00'):bigint>
+-- !query output
+0
+
+
+-- !query
+SELECT time_diff('HOUR', time'00:00:00', time'23:59:59.999999999')
+-- !query schema
+struct<time_diff(HOUR, TIME '00:00:00', TIME '23:59:59.999999'):bigint>
+-- !query output
+23
+
+
+-- !query
+SELECT time_diff('MINUTE', time'00:00:00', time'23:59:59.999999999')
+-- !query schema
+struct<time_diff(MINUTE, TIME '00:00:00', TIME '23:59:59.999999'):bigint>
+-- !query output
+1439
+
+
+-- !query
+SELECT time_diff('SECOND', time'00:00:00', time'23:59:59.999999999')
+-- !query schema
+struct<time_diff(SECOND, TIME '00:00:00', TIME '23:59:59.999999'):bigint>
+-- !query output
+86399
+
+
+-- !query
+SELECT time_diff('MILLISECOND', time'00:00:00', time'23:59:59.999999999')
+-- !query schema
+struct<time_diff(MILLISECOND, TIME '00:00:00', TIME '23:59:59.999999'):bigint>
+-- !query output
+86399999
+
+
+-- !query
+SELECT time_diff('MICROSECOND', time'00:00:00', time'23:59:59.999999999')
+-- !query schema
+struct<time_diff(MICROSECOND, TIME '00:00:00', TIME '23:59:59.999999'):bigint>
+-- !query output
+86399999999
+
+
+-- !query
+SELECT time_diff('', time'00:00:00', time'12:34:56')
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.SparkIllegalArgumentException
+{
+ "errorClass" : "INVALID_PARAMETER_VALUE.TIME_UNIT",
+ "sqlState" : "22023",
+ "messageParameters" : {
+ "functionName" : "`timediff`",
+ "invalidValue" : "''",
+ "parameter" : "`unit`"
+ }
+}
+
+
+-- !query
+SELECT time_diff(' ', time'00:00:00', time'12:34:56')
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.SparkIllegalArgumentException
+{
+ "errorClass" : "INVALID_PARAMETER_VALUE.TIME_UNIT",
+ "sqlState" : "22023",
+ "messageParameters" : {
+ "functionName" : "`timediff`",
+ "invalidValue" : "' '",
+ "parameter" : "`unit`"
+ }
+}
+
+
+-- !query
+SELECT time_diff('MS', time'00:00:00', time'12:34:56')
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.SparkIllegalArgumentException
+{
+ "errorClass" : "INVALID_PARAMETER_VALUE.TIME_UNIT",
+ "sqlState" : "22023",
+ "messageParameters" : {
+ "functionName" : "`timediff`",
+ "invalidValue" : "'MS'",
+ "parameter" : "`unit`"
+ }
+}
+
+
+-- !query
+SELECT time_diff('DAY', time'00:00:00', time'12:34:56')
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.SparkIllegalArgumentException
+{
+ "errorClass" : "INVALID_PARAMETER_VALUE.TIME_UNIT",
+ "sqlState" : "22023",
+ "messageParameters" : {
+ "functionName" : "`timediff`",
+ "invalidValue" : "'DAY'",
+ "parameter" : "`unit`"
+ }
+}
+
+
+-- !query
+SELECT time_diff('WEEK', time'00:00:00', time'12:34:56')
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.SparkIllegalArgumentException
+{
+ "errorClass" : "INVALID_PARAMETER_VALUE.TIME_UNIT",
+ "sqlState" : "22023",
+ "messageParameters" : {
+ "functionName" : "`timediff`",
+ "invalidValue" : "'WEEK'",
+ "parameter" : "`unit`"
+ }
+}
+
+
+-- !query
+SELECT time_diff('ABCD', time'00:00:00', time'12:34:56')
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.SparkIllegalArgumentException
+{
+ "errorClass" : "INVALID_PARAMETER_VALUE.TIME_UNIT",
+ "sqlState" : "22023",
+ "messageParameters" : {
+ "functionName" : "`timediff`",
+ "invalidValue" : "'ABCD'",
+ "parameter" : "`unit`"
+ }
+}
+
+
+-- !query
+SELECT time_diff('QUARTER', time'00:00:00', time'12:34:56')
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.SparkIllegalArgumentException
+{
+ "errorClass" : "INVALID_PARAMETER_VALUE.TIME_UNIT",
+ "sqlState" : "22023",
+ "messageParameters" : {
+ "functionName" : "`timediff`",
+ "invalidValue" : "'QUARTER'",
+ "parameter" : "`unit`"
+ }
+}
+
+
+-- !query
+SELECT time_diff('INVALID', time'00:00:00', time'12:34:56')
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.SparkIllegalArgumentException
+{
+ "errorClass" : "INVALID_PARAMETER_VALUE.TIME_UNIT",
+ "sqlState" : "22023",
+ "messageParameters" : {
+ "functionName" : "`timediff`",
+ "invalidValue" : "'INVALID'",
+ "parameter" : "`unit`"
+ }
+}
+
+
+-- !query
+SELECT time_diff('INVALID_UNIT', time'00:00:00', time'12:34:56')
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.SparkIllegalArgumentException
+{
+ "errorClass" : "INVALID_PARAMETER_VALUE.TIME_UNIT",
+ "sqlState" : "22023",
+ "messageParameters" : {
+ "functionName" : "`timediff`",
+ "invalidValue" : "'INVALID_UNIT'",
+ "parameter" : "`unit`"
+ }
+}
+
+
+-- !query
+SELECT time_diff(NULL, time'00:00:00', time'12:34:56')
+-- !query schema
+struct<time_diff(NULL, TIME '00:00:00', TIME '12:34:56'):bigint>
+-- !query output
+NULL
+
+
+-- !query
+SELECT time_diff('MICROSECOND', NULL, time'12:34:56')
+-- !query schema
+struct<time_diff(MICROSECOND, NULL, TIME '12:34:56'):bigint>
+-- !query output
+NULL
+
+
+-- !query
+SELECT time_diff('MICROSECOND', time'00:00:00', NULL)
+-- !query schema
+struct<time_diff(MICROSECOND, TIME '00:00:00', NULL):bigint>
+-- !query output
+NULL
+
+
+-- !query
+SELECT time_diff(NULL, NULL, time'12:34:56')
+-- !query schema
+struct<time_diff(NULL, NULL, TIME '12:34:56'):bigint>
+-- !query output
+NULL
+
+
+-- !query
+SELECT time_diff(NULL, time'00:00:00', NULL)
+-- !query schema
+struct<time_diff(NULL, TIME '00:00:00', NULL):bigint>
+-- !query output
+NULL
+
+
+-- !query
+SELECT time_diff('MICROSECOND', NULL, NULL)
+-- !query schema
+struct<time_diff(MICROSECOND, NULL, NULL):bigint>
+-- !query output
+NULL
+
+
+-- !query
+SELECT time_diff(NULL, NULL, NULL)
+-- !query schema
+struct<time_diff(NULL, NULL, NULL):bigint>
+-- !query output
+NULL
+
+
+-- !query
+SELECT time_diff('SECOND', time_start, time_end) FROM timediff_view
+-- !query schema
+struct<time_diff(SECOND, time_start, time_end):bigint>
+-- !query output
+10983
+
+
+-- !query
+SELECT time_diff(unit, time'01:02:03', time_end) FROM timediff_view
+-- !query schema
+struct<time_diff(unit, TIME '01:02:03', time_end):bigint>
+-- !query output
+10983
+
+
+-- !query
+SELECT time_diff(unit, time_start, time'04:05:06') FROM timediff_view
+-- !query schema
+struct<time_diff(unit, time_start, TIME '04:05:06'):bigint>
+-- !query output
+10983
+
+
+-- !query
+SELECT time_diff('SECOND', time'01:02:03', time_end) FROM timediff_view
+-- !query schema
+struct<time_diff(SECOND, TIME '01:02:03', time_end):bigint>
+-- !query output
+10983
+
+
+-- !query
+SELECT time_diff('SECOND', time_start, time'04:05:06') FROM timediff_view
+-- !query schema
+struct<time_diff(SECOND, time_start, TIME '04:05:06'):bigint>
+-- !query output
+10983
+
+
+-- !query
+SELECT time_diff(unit, time'01:02:03', time'04:05:06') FROM timediff_view
+-- !query schema
+struct<time_diff(unit, TIME '01:02:03', TIME '04:05:06'):bigint>
+-- !query output
+10983
+
+
+-- !query
+SELECT time_diff(unit, time_start, time_end) FROM timediff_view
+-- !query schema
+struct<time_diff(unit, time_start, time_end):bigint>
+-- !query output
+10983
+
+
-- !query
SELECT TIME'12:30:41' - TIME'10:00'
-- !query schema
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@spark.apache.org
For additional commands, e-mail: commits-help@spark.apache.org