andygrove opened a new issue, #240:
URL: https://github.com/apache/datafusion-spark/issues/240

   Part of #175 and apache/datafusion-comet#7300.
   
   ### What the function does
   
   `timestampdiff(unit, startTimestamp, endTimestamp)` returns `endTimestamp - 
startTimestamp` in whole
   `unit`s as `BIGINT`, truncating any fractional part. In Spark SQL this is a 
grammar rule, not a registry
   function: `TIMESTAMPDIFF`, `DATEDIFF`, `DATE_DIFF` and `TIMEDIFF` followed 
by an unquoted unit keyword all
   parse to the `TimestampDiff` expression (`SqlBaseParser.g4`, rule 
`#timestampdiff`). The DataFrame API
   reaches it through `functions.timestamp_diff(unit: String, start, end)` 
(4.0+), which calls the internal
   function `timestampdiff` with the unit as a string literal. 
datafusion-spark's `date_diff` / `datediff`
   only cover the two-argument day form, so this unit form is missing.
   
   Units (case-insensitive): `YEAR`, `QUARTER`, `MONTH`, `WEEK`, `DAY`, `HOUR`, 
`MINUTE`, `SECOND`,
   `MILLISECOND`, `MICROSECOND`. `inputTypes` is `(TIMESTAMP, TIMESTAMP)` with 
`ImplicitCastInputTypes`, so
   `DATE`, `STRING` and also `TIMESTAMP_NTZ` arguments are cast to `TIMESTAMP` 
in the session time zone (the
   analyzed plan shows `cast(... as timestamp)` for NTZ literals). Both values 
are converted to local
   date-times in `spark.sql.session.timeZone` and the difference is taken with 
`ChronoUnit.<UNIT>.between` on
   those `LocalDateTime`s. Null-intolerant. Since 3.3.0.
   
   `time_diff(unit, start, end)` (4.1.0+) is a different, registry-backed 
function operating on `TIME` values
   (`TimeDiff` in `timeExpressions.scala`); it is out of scope here.
   
   ### Examples
   
   ```sql
   SELECT timestampdiff(HOUR, timestamp_ntz'2022-02-11 20:30:00', 
timestamp_ntz'2022-02-12 04:30:00');
   -- 8
   SELECT timestampdiff(MONTH, timestamp_ltz'2022-01-01 00:00:00', 
timestamp_ltz'2022-02-28 00:00:00');
   -- 1
   SELECT timestampdiff(SECOND, date'2022-01-01', timestamp'2021-12-31 
23:59:50');
   -- -10
   SELECT timestampdiff(YEAR, timestamp'2000-01-01 01:02:03.123456', 
timestamp'2010-01-01 01:02:03.123456');
   -- 10
   SELECT timestampdiff(MICROSECOND, timestamp'2022-02-25 01:02:03.123', 
timestamp'2022-02-25 01:02:03.124001');
   -- 1001
   SELECT timestampdiff(MILLISECOND, timestamp'2022-02-25 01:02:03.456', 
timestamp'2022-02-25 01:02:03.455');
   -- -1
   SELECT timestampdiff(DAY, date'2022-02-25', timestamp'2023-02-27 00:00:00');
   -- 367
   SELECT timestampdiff(WEEK, timestamp'2022-02-25 01:02:03', 
timestamp'2022-01-28 01:02:03');
   -- -4
   SELECT timestampdiff(QUARTER, timestamp'2023-08-10 01:02:03', 
timestamp'2022-01-14 01:02:03');
   -- -6
   SELECT timestampdiff(QUARTER, timestamp_ntz'2022-01-01 01:02:03', 
timestamp_ntz'2022-05-02 05:06:07');
   -- 1
   SELECT timestampdiff(MILLISECOND, timestamp_ntz'2022-02-14 23:59:59.123', 
date'2022-02-15');
   -- 877
   
   -- Errors
   SELECT date_diff('MILLISECOND', timestamp'2022-02-25 01:02:03.456', 
timestamp'2022-02-25 01:02:03.455');
   -- ParseException [INVALID_PARAMETER_VALUE.DATETIME_UNIT] ... expects one of 
the units without quotes
   -- YEAR, QUARTER, MONTH, WEEK, DAY, DAYOFYEAR, HOUR, MINUTE, SECOND, 
MILLISECOND, MICROSECOND,
   -- but got the string literal 'MILLISECOND'.
   ```
   
   From `DateTimeUtilsSuite`: `WEEK` from 2022-02-14 11:43:00.000001 to 
2022-02-21 11:42:59.000001 is `0`
   (truncation); `SECOND` between values 1 microsecond apart is `0`; `YEAR` 
from 9999-01-01 to 0001-01-01 is
   `-9998`.
   
   ### Edge cases to test
   
   - NULL in either timestamp returns NULL.
   - Unit case-insensitivity (`'Hour'` works); unknown unit raises 
`INVALID_PARAMETER_VALUE.DATETIME_UNIT`
     (SQLSTATE 22023) with `functionName` `TIMESTAMPDIFF`. `DAYOFYEAR` is 
accepted by the grammar but is not in
     `timestampDiffMap`, so it fails with that error at runtime (derived from 
the code).
   - Since the unit is a keyword in Spark, a DataFusion function will need it 
as a string literal first
     argument (`timestampdiff('HOUR', a, b)`), matching the DataFrame API path; 
a non-literal unit should be a
     planning error.
   - Truncation toward zero for negative differences (`QUARTER` is 
`MONTHS.between / 3` with Java long
     division).
   - `MONTH`/`QUARTER`/`YEAR` count complete calendar months including day and 
time-of-day:
     `MONTH` from 2022-01-31 to 2022-02-28 is `0` (derived from 
`LocalDate.until`), unlike `months_between`.
   - DST: because the difference is taken on local wall-clock date-times, 
sub-day units measure wall-clock
     distance, not elapsed time. Across the America/Los_Angeles spring-forward 
(2011-03-13 01:59:59 ->
     03:00:00), `HOUR` from local 01:00 to local 03:00 is `2` although one hour 
elapsed, and in the fall-back
     overlap two distinct instants at the same wall-clock time give `0` 
(derived from the code;
     `DateExpressionsSuite` only asserts a non-crossing case, `6` hours from 
`2022-03-12T23:30:00Z` to
     `2022-03-13T05:30:00Z` in LA). Match this rather than subtracting epoch 
microseconds.
   - `TIMESTAMP_NTZ` inputs are first cast to `TIMESTAMP` with the session 
zone, then converted back to local
     time in the same zone; NTZ values that fall into a DST gap are shifted by 
the cast.
   - Mixed `TIMESTAMP_NTZ` / `TIMESTAMP` / `DATE` arguments (golden: `HOUR` 
from NTZ 2022-02-14 01:02:03 to
     `TIMESTAMP` 2022-02-14 02:03:04 is `1`; `YEAR` from `DATE` 2022-02-15 to 
NTZ 2023-02-15 10:11:12 is `1`).
   - Leap years: `DAY` from `2020-02-28T10:11:12Z` to `2020-03-01T10:21:12Z` in 
LA is `2`.
   - Very large spans: `ChronoUnit.MICROS.between` uses exact arithmetic, so a 
difference that does not fit in a
     long (e.g. between timestamps near the opposite ends of the microsecond 
range) throws a raw
     `java.lang.ArithmeticException` (not caught by Spark; derived from the 
code). No ANSI dependency.
   - Scalar and array for each timestamp argument.
   
   ### Differences across Spark versions
   
   - 3.4: only `TIMESTAMPDIFF` and `DATEDIFF` in the grammar; a quoted unit is 
a syntax error.
   - 3.5: `DATE_DIFF` added, and a quoted unit gives 
`INVALID_PARAMETER_VALUE.DATETIME_UNIT`.
   - 4.0: `TIMEDIFF` added as another keyword for the same rule; 
`functions.timestamp_diff` added to the
     DataFrame API.
   - 4.1: separate `time_diff` function for `TIME` values added (distinct 
expression).
   - The diff semantics (`timestampDiffMap`, `inputTypes = (TimestampType, 
TimestampType)`) are unchanged from
     3.4 to 4.2.
   
   ### Implementation notes
   
   - Spark: `TimestampDiff` in
     
`sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/datetimeExpressions.scala`,
     `DateTimeUtils.timestampDiff` in 
`sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/DateTimeUtils.scala`,
     parser in 
`sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala`
     (`visitTimestampdiff`).
   - DataFusion core has no `timestampdiff`; `date_part` / timestamp 
subtraction work on elapsed time and do not
     reproduce `ChronoUnit.between` on local date-times.
   - Suggested shape: a new `timestampdiff` UDF in 
`crates/spark/src/function/datetime/` taking
     `(Utf8 literal unit, Timestamp, Timestamp)` returning `Int64`. chrono's 
`NaiveDateTime` arithmetic can
     mirror `LocalDateTime.until`. Reuse `timezone.rs` (`SparkTimeZone`) and 
read the session zone from
     `config_options().execution.time_zone` as `date_trunc.rs` does. Whether 
`date_diff` / `datediff` should
     also accept the 3-argument form is a separate decision.
   - Add `crates/sqllogictest/test_files/datetime/timestampdiff.slt`; 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