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

   Part of #175 and apache/datafusion-comet#7300.
   
   ### What the function does
   
   `timestampadd(unit, quantity, timestamp)` adds `quantity` units to a 
timestamp. In Spark SQL this is a
   grammar rule, not a registry function: `TIMESTAMPADD`, `DATEADD` and 
`DATE_ADD` followed by an unquoted unit
   keyword all parse to the `TimestampAdd` expression (`SqlBaseParser.g4`, rule 
`#timestampadd`). The
   DataFrame API reaches the same expression through 
`functions.timestamp_add(unit: String, ...)` (4.0+),
   which calls the internal function `timestampadd` with the unit as a string 
literal. datafusion-spark's
   `date_add` / `dateadd` only cover the two-argument `date_add(start_date, 
num_days)` form, so this unit form
   is missing.
   
   Units (case-insensitive): `YEAR`, `QUARTER` (3 months), `MONTH`, `WEEK` (7 
days), `DAY`, `DAYOFYEAR`
   (same as `DAY`), `HOUR`, `MINUTE`, `SECOND`, `MILLISECOND`, `MICROSECOND`. 
`inputTypes` is
   `(BIGINT, AnyTimestampType)` with `ImplicitCastInputTypes`. `timestamp` may 
be `TIMESTAMP` or
   `TIMESTAMP_NTZ`; `DATE` and `STRING` are implicitly cast to `TIMESTAMP`. The 
return type equals the
   (post-cast) timestamp type, so `DATE` input returns `TIMESTAMP`. 
Null-intolerant. `TIMESTAMP` values are
   computed in `spark.sql.session.timeZone`; `TIMESTAMP_NTZ` values in UTC. 
Since 3.3.0.
   
   ### Examples
   
   ```sql
   SELECT timestampadd(HOUR, 8, timestamp_ntz'2022-02-11 20:30:00');
   -- 2022-02-12 04:30:00
   SELECT timestampadd(MONTH, 1, timestamp_ltz'2022-01-31 00:00:00');
   -- 2022-02-28 00:00:00
   SELECT timestampadd(SECOND, -10, date'2022-01-01');
   -- 2021-12-31 23:59:50
   SELECT timestampadd(YEAR, 10, timestamp'2000-01-01 01:02:03.123456');
   -- 2010-01-01 01:02:03.123456
   SELECT timestampadd(MICROSECOND, 1001, timestamp'2022-02-25 01:02:03.123');
   -- 2022-02-25 01:02:03.124001
   SELECT timestampadd(MINUTE, -100, date'2022-02-25');
   -- 2022-02-24 22:20:00
   SELECT timestampadd(DAY, 367, date'2022-02-25');
   -- 2023-02-27 00:00:00
   SELECT timestampadd(WEEK, -4, timestamp'2022-02-25 01:02:03');
   -- 2022-01-28 01:02:03
   SELECT timestampadd(QUARTER, 5, date'2022-02-25');
   -- 2023-05-25 00:00:00
   
   -- DST, spark.sql.session.timeZone=America/Los_Angeles (2011-03-13 has 23 
hours)
   -- timestampadd(DAY, 1, 2011-03-12 03:00:00)              = 2011-03-13 
03:00:00
   -- timestampadd(HOUR, 24, 2011-03-12 03:00:00)            = 2011-03-13 
04:00:00
   -- timestampadd(SECOND, 86400, 2011-03-12 03:00:00)       = 2011-03-13 
04:00:00
   
   -- Errors
   SELECT dateadd('MICROSECOND', 1001, timestamp'2022-02-25 01:02:03.123');
   -- 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 'MICROSECOND'.
   -- timestampadd(DAY, 106751992, TIMESTAMP '1970-01-01 00:00:00') in UTC:
   -- [DATETIME_OVERFLOW] Datetime operation overflow: add 106751992L DAY to 
TIMESTAMP '1970-01-01 00:00:00'.
   ```
   
   The DST rows are from `DateExpressionsSuite` ("SPARK-42635: timestampadd 
near daylight saving transition").
   
   ### Edge cases to test
   
   - NULL `quantity` or `timestamp` returns NULL.
   - Unit case-insensitivity; unknown unit raises 
`INVALID_PARAMETER_VALUE.DATETIME_UNIT` (SQLSTATE 22023) with
     `functionName` `TIMESTAMPADD`. Since the unit is a keyword in Spark, a 
DataFusion function will need it
     as a string literal first argument (`timestampadd('HOUR', 8, ts)`), 
matching the DataFrame API path; a
     non-literal unit should be a planning error.
   - Semantics per unit (from `DateTimeUtils.timestampAdd`): 
`MONTH`/`QUARTER`/`YEAR` use
     `ZonedDateTime.plusMonths` (clamping to month end: Jan 31 + 1 month = Feb 
28); `DAY`/`DAYOFYEAR`/`WEEK`
     use `plusDays` on the local date-time in the session zone; `HOUR` and 
smaller add exact elapsed
     microseconds to the instant. So across DST, `DAY 1` keeps wall-clock time 
but `HOUR 24` does not.
   - DST overlap (America/Los_Angeles 2011-11-06 01:00-02:00): 
`plusDays`/`plusMonths` keep the input's offset
     when it is valid for the ambiguous result, e.g. `DAY 1` from 2011-11-05 
01:00 lands on the pre-transition 01:00 and
     `DAY -1` from 2011-11-07 01:00 lands on the post-transition 01:00 (both 
asserted in the same suite).
     DST gap results shift forward by the gap length.
   - `TIMESTAMP_NTZ` input: same arithmetic in UTC, so no DST effects; result 
stays `TIMESTAMP_NTZ`.
   - Overflow raises `DATETIME_OVERFLOW` (SQLSTATE 22008) regardless of ANSI 
mode: `quantity * unit` overflowing
     a long (e.g. `Long.MaxValue` with `MILLISECOND` .. `WEEK`), `quantity` 
outside INT range for `DAY` and
     larger units (`Math.toIntExact`), `QUARTER`/`YEAR` month count overflowing 
INT
     (`timestampadd(QUARTER, 1431655764, ...)`), and results outside the 
microsecond `Long` range.
   - Large quantities that fit: `timestampadd(MICROSECOND, 10000000000, 
TIMESTAMP '1970-01-01 00:00:00')` in UTC
     works (BIGINT quantity, 4.0+).
   - Results past year 9999 and before year 1 are allowed for `TIMESTAMP` as 
long as they fit in microseconds.
   - `quantity` types: integral types are widened to BIGINT; `STRING` and 
fractional/decimal types are
     implicitly cast to BIGINT in both ANSI and non-ANSI coercion 
(`Cast.canANSIStoreAssign` allows any
     numeric to numeric), so `1.9` becomes `1`; with ANSI on a malformed string 
fails with `CAST_INVALID_INPUT`.
   - `DATE` and `STRING` timestamp argument are cast to `TIMESTAMP` at 
session-zone midnight / parsed.
   - Scalar and array for `quantity` and `timestamp`.
   
   ### Differences across Spark versions
   
   - 3.4: only `TIMESTAMPADD` and `DATEADD` in the grammar; a quoted unit is a 
syntax error.
   - 3.5: `DATE_ADD` added to the rule, and a quoted unit gives 
`INVALID_PARAMETER_VALUE.DATETIME_UNIT`.
   - 3.4/3.5: `quantity` is `INT` (`inputTypes = (IntegerType, 
AnyTimestampType)`) and the overflow message
     shows an INT literal; an unknown unit inside `timestampAdd` is an 
`IllegalStateException`.
   - 4.0 (SPARK-50669): `quantity` widened to `BIGINT`; 
`functions.timestamp_add` added to the DataFrame API.
   - 4.1/4.2: same as 4.0.
   
   ### Implementation notes
   
   - Spark: `TimestampAdd` in
     
`sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/datetimeExpressions.scala`,
     `DateTimeUtils.timestampAdd` / `timestampAddInterval` / 
`timestampAddMonths` 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`
 (`visitTimestampadd`).
   - DataFusion core has no `timestampadd`. Timestamp `+ INTERVAL` arithmetic 
in arrow does not apply the
     session zone's DST rules the way Spark's `ZonedDateTime` arithmetic does, 
so it cannot be reused directly
     for `DAY`/`WEEK`/`MONTH`.
   - Suggested shape: a new `timestampadd` UDF in 
`crates/spark/src/function/datetime/` taking
     `(Utf8 literal unit, Int64, Timestamp)`. Whether `date_add` / `dateadd` 
should also accept the 3-argument
     form is a separate decision; Comet can call `timestampadd` directly. Reuse 
`timezone.rs`
     (`SparkTimeZone`) and read the session zone from 
`config_options().execution.time_zone` as
     `date_trunc.rs` does.
   - Add `crates/sqllogictest/test_files/datetime/timestampadd.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