andygrove opened a new issue, #210:
URL: https://github.com/apache/datafusion-spark/issues/210
Part of #178 and apache/datafusion-comet#7300.
### What the function does
`time_diff(unit, start, end)` returns the difference `end - start` between
two TIME values, expressed as a
whole number of `unit`s. `unit` is a STRING (any collation, including trim
collations) and must be one of
`HOUR`, `MINUTE`, `SECOND`, `MILLISECOND`, `MICROSECOND`, matched
case-insensitively. `start` and `end`
accept TIME of any precision (`AnyTimeType`, so `TIME(0)` .. `TIME(6)`,
mixed precisions allowed). The
result is BIGINT. It is NULL if any argument is NULL.
The computation is `(endNanos - startNanos) / nanosPerUnit` with Java `Long`
division, so the result is
truncated toward zero (not floored) and is negative when `end < start`.
There is no wrap-around at midnight.
Added in Spark 4.1.0. Like every TIME function, it is only usable when
`spark.sql.timeType.enabled` is true
(internal config, default `false` outside Spark's own tests); otherwise
analysis fails with
`UNSUPPORTED_TIME_TYPE` ("The data type TIME is not supported."). No aliases.
### Examples
```sql
SELECT time_diff('HOUR', TIME'20:30:29', TIME'21:30:28');
-- 0
SELECT time_diff('HOUR', TIME'20:30:29', TIME'21:30:29');
-- 1
SELECT time_diff('HOUR', TIME'20:30:29', TIME'12:00:00');
-- -8
SELECT time_diff('MINUTE', time'00:00:00', time'12:34:56');
-- 754
SELECT time_diff('SECOND', time'12:34:56', time'01:02:03');
-- -41573
SELECT time_diff('MILLISECOND', time'00:00:00', time'12:34:56.123456');
-- 45296123
SELECT time_diff('MICROSECOND', time'00:00:00', time'12:34:56.123456');
-- 45296123456
SELECT time_diff('MICROSECOND', time'00:00:00', time'23:59:59.999999999');
-- 86399999999 (literal is truncated to TIME(6) = 23:59:59.999999)
SELECT time_diff('MiNuTe', time'00:00:00', time'12:34:56');
-- 754
SELECT time_diff(NULL, time'00:00:00', time'12:34:56');
-- NULL
SELECT time_diff('DAY', time'00:00:00', time'12:34:56');
-- error INVALID_PARAMETER_VALUE.TIME_UNIT
```
### Edge cases to test
- NULL in each argument and in every combination (all return NULL, including
a NULL unit with valid times).
- Truncation toward zero on negative differences: `HOUR` from `20:30:29` to
`12:00:00` is `-8`, not `-9`.
- `start == end` returns `0` for every unit.
- Sub-unit differences: `time'12:34:56.1'` gives `45296` for `SECOND`,
`45296100` for `MILLISECOND`.
- Invalid units throw at execution time with
`INVALID_PARAMETER_VALUE.TIME_UNIT` (SQLSTATE `22023`):
`The value of parameter(s) `unit` in `time_diff` is invalid: expects one
of the units 'HOUR', 'MINUTE',
'SECOND', 'MILLISECOND', 'MICROSECOND', but got '<unit>'.` Spark's tests
cover `''`, `' '`, `'MS'`,
`'DAY'`, `'WEEK'`, `'QUARTER'`, `'ABCD'`, `'INVALID'`. Units are not
trimmed (`' '` is an error), and
`DAY`/`WEEK` etc. are rejected (unlike `timestampdiff`).
- `unit` as a column, not only a literal: Spark validates per row, so the
error must be raised (or not) per
row. `SELECT time_diff(unit, time_start, time_end) FROM v` with `unit =
'SECOND'`,
`time_start = 01:02:03`, `time_end = 04:05:06` returns `10983`. Cover
literal/column for each of the
three arguments.
- Mixed precisions (`TIME(0)` vs `TIME(6)`) give the same result as
comparing the stored values.
- Full-range: `00:00:00` to `23:59:59.999999` is `23` hours, `1439` minutes,
`86399` seconds.
- Behaviour does not depend on `spark.sql.ansi.enabled` or the session time
zone.
### Differences across Spark versions
Not present before 4.1.0. The expression and `DateTimeUtils.timeDiff` are
identical in 4.1.3 and 4.2.0, and
the golden results in `time.sql.out` match.
### Implementation notes
- Spark:
`sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/timeExpressions.scala`
(`TimeDiff`, a `RuntimeReplaceable` that calls `DateTimeUtils.timeDiff` /
`getNanosPerTimeUnit` in
`sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/DateTimeUtils.scala`).
Error builder:
`QueryExecutionErrors.invalidTimeUnitError`.
- Spark stores TIME as nanoseconds since midnight and maps every `TIME(p)`
to Arrow
`Time64(Nanosecond)` (`ArrowUtils.toArrowType`). The kernel should accept
at least `Time64(Nanosecond)`
and ideally any Arrow time type (`TypeSignatureClass::Time`) and normalize
to nanoseconds.
- DataFusion core has no `time_diff`. `date_part` on time values and `Time64
- Time64` arithmetic exist but
neither returns a whole count of a named unit with truncation toward zero,
so write a small kernel.
- Reuse in datafusion-spark:
`crates/spark/src/function/datetime/time_trunc.rs` already validates the same
five units (case-insensitive) and emits Spark's
`INVALID_PARAMETER_VALUE.TIME_UNIT` text with
SQLSTATE 22023; factor that into a shared helper. Note `time_trunc`
requires a literal unit, while
`time_diff` must also handle a column unit. Put the new function in the
`datetime` module.
- Tests: add `crates/sqllogictest/test_files/datetime/time_diff.slt` (cases
above come from Spark's
`sql/core/src/test/resources/sql-tests/results/time.sql.out` and
`TimeExpressionsSuite`
"SPARK-51555: Time difference"), and document with `#[user_doc]`.
---
_This issue was generated by an LLM (Claude) from the Apache Spark source
code and tests. Examples come from Spark's `ExpressionDescription` examples,
test suites and `.sql.out` golden files, but please verify them against Spark
before relying on them._
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]