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]

Reply via email to