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]

Reply via email to