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]