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]

Reply via email to