andygrove opened a new issue, #231:
URL: https://github.com/apache/datafusion-spark/issues/231
Part of #175 and apache/datafusion-comet#7300.
### What the function does
`make_timestamp_ltz(year, month, day, hour, min, sec[, timezone])` builds a
`TIMESTAMP` (with local time zone) from
fields. It is `make_timestamp` with the result type fixed to `TIMESTAMP`,
whatever `spark.sql.timestampType` says.
`year`..`min` are implicitly cast to `INT`, `sec` to `DECIMAL(16, 6)` and
`timezone` to `STRING`. Fields are a local
date-time in `timezone`, or in `spark.sql.session.timeZone` when omitted.
`sec = 60` with zero fraction rolls over to
the next minute.
Since 4.1 there is also `make_timestamp_ltz(date, time[, timezone])`
(`DATE`, `TIME(p)`, `STRING`; no implicit cast for
`date`/`time`). Unlike `make_timestamp`, there is no 1-argument form.
Invalid fields return NULL when `spark.sql.ansi.enabled` is false and raise
an error when it is true.
`try_make_timestamp_ltz` (since 4.0) always returns NULL for invalid fields
in the 6/7-argument form. `make_timestamp_ltz`
exists since 3.4.0. Both are registered with `setAlias = true`, so the
column name uses the called name for the
6/7-argument form, but the 2/3-argument form is named `make_timestamp(...)`.
### Examples
Session time zone `America/Los_Angeles` (as in Spark's docs and golden
files):
```sql
SELECT make_timestamp_ltz(2014, 12, 28, 6, 30, 45.887);
-- 2014-12-28 06:30:45.887
SELECT make_timestamp_ltz(2014, 12, 28, 6, 30, 45.887, 'CET');
-- 2014-12-27 21:30:45.887
SELECT make_timestamp_ltz(2019, 6, 30, 23, 59, 60);
-- 2019-07-01 00:00:00
SELECT make_timestamp_ltz(null, 7, 22, 15, 30, 0);
-- NULL
SELECT make_timestamp_ltz(DATE'2014-12-28', TIME'6:30:45.887', 'CET');
-- 2014-12-27 21:30:45.887
SELECT make_timestamp_ltz(make_date(2021, 7, 11), make_time(6, 30, 45.678),
'PST');
-- 2021-07-11 06:30:45.678
SELECT make_timestamp_ltz(DATE'1970-01-01', NULL);
-- NULL
-- spark.sql.ansi.enabled=true
SELECT make_timestamp_ltz(2021, 07, 11, 6, 30, 60.007);
-- [INVALID_FRACTION_OF_SECOND] secAndMicros = 60.007, SQLSTATE 22023
SELECT try_make_timestamp_ltz(2024, 13, 22, 15, 30, 0);
-- NULL
SELECT try_make_timestamp_ltz(DATE'2014-12-28', TIME'6:30:45.887', 'CET');
-- 2014-12-27 21:30:45.887
```
### Edge cases to test
- NULL in each argument of every overload.
- Out of range fields (`month` 13, `day` 32, Feb 29 non-leap, `hour` 25,
`min` 65, `sec` 61, negative `sec`):
NULL with ANSI off, `DATETIME_FIELD_OUT_OF_BOUNDS.WITH_SUGGESTION`
(SQLSTATE 22023) with ANSI on.
`sec` in `(60, 61)` is `INVALID_FRACTION_OF_SECOND` under ANSI.
`try_make_timestamp_ltz` returns NULL for all of these.
- `sec = 60` rollover across minute/day/year boundaries; microsecond
fraction kept (`58.000001`); `sec` with more than
6 fractional digits is rounded by the cast to `DECIMAL(16, 6)`.
- `spark.sql.timestampType=TIMESTAMP_NTZ` must not change the result type
(still `TIMESTAMP`, timezone still applied).
- DST: gap local times shift forward by the gap length, overlap local times
take the earlier offset
(`LocalDateTime.atZone`), e.g. `2019-03-10 02:30` / `2019-11-03 01:30` in
`America/Los_Angeles`.
- `timezone` spellings: region IDs, offsets (`+01:00`, `-08`, `GMT+1`,
`UTC`, `Z`), `ZoneId.SHORT_IDS` (`PST`, `CET`),
legacy `+h:mm`. Invalid zone raises `INVALID_TIMEZONE` (4.x).
- `try_make_timestamp_ltz(date, time[, tz])` is built as a plain
`MakeTimestampFromDateTime` without a `TryEval` wrapper,
so it does not suppress errors such as `INVALID_TIMEZONE`; mirror that or
document the difference.
- Year 0 / negative years / years above 9999 work through `java.time` as
long as the result fits in `i64` microseconds;
past that `ArithmeticException` is thrown even with ANSI off.
- Literal vs column for each argument, including a per-row `timezone`.
- `make_timestamp_ltz(TIMESTAMP_NTZ, TIME)` and `(DATE, TIMESTAMP_NTZ)` fail
analysis with
`DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE`; 4/5/8 arguments raise
`WRONG_NUM_ARGS`.
### Differences across Spark versions
- 3.4 / 3.5: 6/7-argument form only; ANSI off by default; ANSI out-of-range
error is `_LEGACY_ERROR_TEMP_2000`;
`INVALID_FRACTION_OF_SECOND` uses the older "The fraction of sec must be
zero..." message.
- 4.0: ANSI on by default; `try_make_timestamp_ltz` added;
`DATETIME_FIELD_OUT_OF_BOUNDS`; `INVALID_TIMEZONE` added.
- 4.1: `(date, time[, timezone])` overloads added; error becomes
`DATETIME_FIELD_OUT_OF_BOUNDS.WITH_SUGGESTION`.
- 4.2: none found.
### Implementation notes
- Spark:
`sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/datetimeExpressions.scala`,
`MakeTimestampLTZExpressionBuilder`,
`TryMakeTimestampLTZExpressionBuilder`, which build `MakeTimestamp(...,
dataType = TimestampType)` or `MakeTimestampFromDateTime`.
- DataFusion core has no equivalent (only `make_date` / `make_time`).
- Share the kernel with `make_timestamp` (separate issue); this function is
the LTZ-only entry point, returning
`Timestamp(Microsecond, Some(tz))`. Session zone from
`datafusion.execution.time_zone`, ANSI from
`datafusion.execution.enable_ansi_mode`. Reuse `SparkTimeZone` in
`crates/spark/src/function/datetime/timezone.rs`
plus a local-to-instant helper with Java's gap/overlap rules.
- Tests: `crates/sqllogictest/test_files/datetime/make_timestamp_ltz.slt`
exists but every query is commented out (ported
placeholders). Uncomment and expand it, and add
`try_make_timestamp_ltz.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]