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]