andygrove opened a new issue, #232:
URL: https://github.com/apache/datafusion-spark/issues/232
Part of #175 and apache/datafusion-comet#7300.
### What the function does
`make_timestamp_ntz(year, month, day, hour, min, sec)` builds a
`TIMESTAMP_NTZ` (local date-time, no time zone) from
fields. `year`..`min` are implicitly cast to `INT` and `sec` to `DECIMAL(16,
6)`, so fractional seconds keep
microsecond precision. `sec = 60` with a zero fraction rolls over to the
next minute. There is no `timezone` argument;
the session time zone has no effect on the result.
Since 4.1 there is also `make_timestamp_ntz(date, time)` (`DATE`, `TIME(p)`;
no implicit cast, so a `STRING` or
`DOUBLE` time is an analysis error).
Invalid fields return NULL when `spark.sql.ansi.enabled` is false and raise
an error when it is true.
`try_make_timestamp_ntz` (since 4.0) returns NULL instead (6-argument form
uses `failOnError = false`; 2-argument form
is wrapped in `TryEval`). `make_timestamp_ntz` exists since 3.4.0. NULL in
any argument gives NULL.
### Examples
```sql
SELECT make_timestamp_ntz(2014, 12, 28, 6, 30, 45.887);
-- 2014-12-28 06:30:45.887
SELECT make_timestamp_ntz(2019, 6, 30, 23, 59, 60);
-- 2019-07-01 00:00:00
SELECT make_timestamp_ntz(null, 7, 22, 15, 30, 0);
-- NULL
SELECT make_timestamp_ntz(DATE'2014-12-28', TIME'6:30:45.887');
-- 2014-12-28 06:30:45.887
SELECT make_timestamp_ntz(DATE'1970-01-01', NULL);
-- NULL
-- spark.sql.ansi.enabled=true
SELECT make_timestamp_ntz(2021, 07, 11, 6, 30, 60.007);
-- [INVALID_FRACTION_OF_SECOND] secAndMicros = 60.007, SQLSTATE 22023
SELECT make_timestamp_ntz(2021, 07, 11, 6, 30, 45.678, 'CET');
-- [WRONG_NUM_ARGS.WITHOUT_SUGGESTION] expectedNum = [2, 6], actualNum = 7
SELECT try_make_timestamp_ntz(2024, 13, 22, 15, 30, 0);
-- NULL
SELECT try_make_timestamp_ntz(make_date(2025, 6, 27), make_time(16, 08,
45.123456));
-- 2025-06-27 16:08:45.123456
```
### Edge cases to test
- NULL in each argument of both overloads.
- Out of range fields (`month` 0/13, `day` 32, Feb 29 non-leap, `hour` 25,
`min` 65, `sec` 61, 99.999999, 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_ntz` returns NULL for all.
- `sec = 60` rollover across minute/day/year boundaries; `58.000001` keeps
the microsecond; more than 6 fractional
digits are rounded by the implicit cast to `DECIMAL(16, 6)`.
- Results must be identical for every session time zone and must not move
across DST transitions (for example
`2019-03-10 02:30:00` is valid as NTZ even though it does not exist in
`America/Los_Angeles`).
- Years 0, negative and above 9999 are accepted by
`java.time.LocalDateTime`; a result outside the `i64` microsecond
range raises `ArithmeticException` even with ANSI off. Calendar is
proleptic Gregorian.
- 2-argument form: `TIME(p)` precisions, `TIME'23:59:59.999999'`,
`DATE'0001-01-01'`; `(TIMESTAMP_NTZ, TIME)`,
`(DATE, STRING)`, `(DATE, DOUBLE)` fail with
`DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE`.
- 7 arguments fail with `WRONG_NUM_ARGS`; note `try_make_timestamp_ntz`
reports expected `[6]` even though it accepts 2.
- Literal vs column for each argument.
### Differences across Spark versions
- 3.4 / 3.5: 6-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_ntz` added;
`DATETIME_FIELD_OUT_OF_BOUNDS`.
- 4.1: `(date, time)` overloads added (`MakeTimestampNTZ`); 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`,
`MakeTimestampNTZExpressionBuilder`,
`TryMakeTimestampNTZExpressionBuilder`, `MakeTimestamp(..., dataType =
TimestampNTZType)` and `MakeTimestampNTZ` (calls
`DateTimeUtils.makeTimestampNTZ(days, nanos)`).
- DataFusion core has no equivalent (only `make_date` / `make_time`).
- Share the field-validation kernel with `make_timestamp` /
`make_timestamp_ltz` (separate issues); this one returns
`Timestamp(Microsecond, None)` and needs no time zone handling. ANSI from
`datafusion.execution.enable_ansi_mode`.
Belongs in `crates/spark/src/function/datetime/`.
- Tests: `crates/sqllogictest/test_files/datetime/make_timestamp_ntz.slt`
exists but every query is commented out (ported
placeholders). Uncomment and expand it, and add
`try_make_timestamp_ntz.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]