andygrove opened a new issue, #230:
URL: https://github.com/apache/datafusion-spark/issues/230

   Part of #175 and apache/datafusion-comet#7300.
   
   ### What the function does
   
   `make_timestamp(year, month, day, hour, min, sec[, timezone])` builds a 
timestamp from fields. `year`..`min` are
   implicitly cast to `INT`, `sec` to `DECIMAL(16, 6)` (so fractional seconds 
keep microsecond precision) and `timezone`
   to `STRING`. The fields are interpreted as a local date-time in `timezone`, 
or in the session time zone
   (`spark.sql.session.timeZone`) when it is omitted. `sec = 60` with a zero 
fraction is accepted and rolls over to the
   next minute (PostgreSQL compatibility).
   
   The result type of the 6/7-argument form follows `spark.sql.timestampType` 
(default `TIMESTAMP_LTZ`, so `TIMESTAMP`).
   With `TIMESTAMP_NTZ` the result is `TIMESTAMP_NTZ` and the `timezone` 
argument is ignored. Since 4.1 there is also
   `make_timestamp(date[, time[, timezone]])` (`DATE`, `TIME(p)`, `STRING`, no 
implicit cast for `date`/`time`), which
   always returns `TIMESTAMP`; a missing `time` means midnight.
   
   Invalid fields (month 13, sec 61, sec 60.5, ...) return NULL when 
`spark.sql.ansi.enabled` is false and raise an
   error when it is true. `try_make_timestamp` (since 4.0) has the same 
overloads and always returns NULL for invalid
   fields. `make_timestamp` exists since 3.0.0. Result is nullable when ANSI is 
off; NULL in any argument gives NULL.
   
   ### Examples
   
   Session time zone `America/Los_Angeles` (as in Spark's docs and golden 
files):
   
   ```sql
   SELECT make_timestamp(2014, 12, 28, 6, 30, 45.887);
   -- 2014-12-28 06:30:45.887
   SELECT make_timestamp(2014, 12, 28, 6, 30, 45.887, 'CET');
   -- 2014-12-27 21:30:45.887
   SELECT make_timestamp(2019, 6, 30, 23, 59, 60);
   -- 2019-07-01 00:00:00
   SELECT make_timestamp(1, 1, 1, 1, 1, 59.999999);
   -- 0001-01-01 01:01:59.999999
   SELECT make_timestamp(null, 7, 22, 15, 30, 0);
   -- NULL
   SELECT make_timestamp(DATE'2014-12-28');
   -- 2014-12-28 00:00:00
   SELECT make_timestamp(DATE'2014-12-28', TIME'6:30:45.887', 'CET');
   -- 2014-12-27 21:30:45.887
   SELECT make_timestamp(make_date(2021, 7, 11), make_time(6, 30, 45.678), 
'PST');
   -- 2021-07-11 06:30:45.678
   SELECT make_timestamp(DATE'1970-01-01', TIME'00:00:00', 'UTC');
   -- 1969-12-31 16:00:00
   
   -- spark.sql.ansi.enabled=false
   SELECT make_timestamp(2021, 7, 11, 6, 30, 60.007);  -- NULL
   SELECT make_timestamp(1, 1, 1, 1, 1, 61);            -- NULL
   -- spark.sql.ansi.enabled=true
   SELECT make_timestamp(2021, 7, 11, 6, 30, 60.007);
   -- [INVALID_FRACTION_OF_SECOND] secAndMicros = 60.007, SQLSTATE 22023
   SELECT make_timestamp(1, 1, 1, 1, 1, 61);
   -- [DATETIME_FIELD_OUT_OF_BOUNDS.WITH_SUGGESTION]
   --   rangeMessage = "Invalid value for SecondOfMinute (valid values 0 - 59): 
61"
   
   SELECT try_make_timestamp(2024, 13, 22, 15, 30, 0);
   -- NULL
   SELECT try_make_timestamp(DATE'2014-12-28', TIME'6:30:45.887', 'CET');
   -- 2014-12-27 21:30:45.887
   
   -- spark.sql.timestampType=TIMESTAMP_NTZ: result type is timestamp_ntz and 
'CET' is ignored
   SELECT make_timestamp(2021, 07, 11, 6, 30, 45.678, 'CET');
   -- 2021-07-11 06:30:45.678
   ```
   
   ### Edge cases to test
   
   - NULL in each of the 6/7 arguments, and in each argument of the 
1/2/3-argument overloads.
   - Out of range fields: `month` 0/13, `day` 32, Feb 29 in a non-leap year, 
`hour` 25, `min` 65, `sec` 61, 99.999999,
     999.999999, negative `sec`, `year = Int.MaxValue`. NULL with ANSI off, 
`DATETIME_FIELD_OUT_OF_BOUNDS.WITH_SUGGESTION`
     (SQLSTATE 22023) with ANSI on; `rangeMessage` is the `java.time` message, 
e.g.
     "Invalid value for SecondOfMinute (valid values 0 - 59): 61".
   - `sec = 60` exactly rolls over (including into the next day/year); `sec = 
60.000001`..`60.999999` is
     `INVALID_FRACTION_OF_SECOND` under ANSI, NULL otherwise. 
`try_make_timestamp` returns NULL in all of these.
   - Microsecond precision: `sec = 58.000001` gives `...:58.000001`. A `sec` 
with more than 6 fractional digits is rounded
     by the cast to `DECIMAL(16, 6)` before evaluation; double `sec` (e.g. 
`45.887D`) also goes through that cast.
   - Years: Spark documents 1..9999, but the code uses 
`java.time.LocalDateTime`, so year 0, negative years and years above
     9999 produce values as long as the microsecond result fits in an `i64`. A 
result outside the `i64` microsecond range
     raises `ArithmeticException` from `Math.multiplyExact`/`addExact` (not 
caught, so even `try_make_timestamp` and
     ANSI-off throw). Check the conversion is proleptic Gregorian (no Julian 
switch before 1582).
   - DST, LTZ result only: a local time in a gap (e.g. `2019-03-10 02:30` in 
`America/Los_Angeles`) is moved forward by
     the gap length, and a local time in an overlap (`2019-11-03 01:30`) uses 
the earlier offset (`LocalDateTime.atZone`
     semantics). `TIMESTAMP_NTZ` results are not affected by any time zone.
   - `timezone` forms: region IDs, `+01:00`, `-08`, `GMT+1`, `UTC`, `Z`, Java 
short IDs (`PST`, `CET`, `MIT`, `JST`) via
     `ZoneId.SHORT_IDS`, and Spark's legacy `+h:mm` / `+hh:m` spellings. An 
invalid zone raises `INVALID_TIMEZONE`
     (SQLSTATE 22009) in 4.x. In the 7-argument form the zone is resolved 
outside the `try` in interpreted mode but inside it
     in codegen, so with ANSI off codegen returns NULL while interpreted mode 
throws; check against real Spark.
   - Literal vs column for every argument, including a per-row `timezone` 
column.
   - Overloads: `make_timestamp(TIMESTAMP, TIME, ...)` / `(DATE, TIMESTAMP_NTZ, 
...)` / `(DATE, STRING)` fail analysis with
     `DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE`; wrong arg count (4, 5, 8) is 
`WRONG_NUM_ARGS` with expected `[1, 2, 3, 6, 7]`.
   - The date/time overload with an explicit `timezone` and 
`spark.sql.timestampType=TIMESTAMP_NTZ` still returns `TIMESTAMP`.
   - `TIME(p)` with p < 6 and `TIME(6)` fractions (`TIME'23:59:59.999999'`).
   
   ### Differences across Spark versions
   
   - 3.4 / 3.5: only the 6/7-argument form. ANSI off by default. Out of range 
under ANSI is `_LEGACY_ERROR_TEMP_2000`;
     `INVALID_FRACTION_OF_SECOND` has message "The fraction of sec must be 
zero. Valid range is [0, 60]. If necessary set
     <ansiConfig> to "false" to bypass this error." An invalid zone is a raw 
`java.time.DateTimeException`.
   - 4.0: ANSI on by default. `try_make_timestamp` added (6/7 args). Out of 
range is `DATETIME_FIELD_OUT_OF_BOUNDS` (no
     subclass). `INVALID_FRACTION_OF_SECOND` message now reports `secAndMicros` 
and suggests `try_make_timestamp`.
     `INVALID_TIMEZONE` added.
   - 4.1: the `(date[, time[, timezone]])` overloads are added for both 
functions (needs the `TIME` type). Out of range
     becomes `DATETIME_FIELD_OUT_OF_BOUNDS.WITH_SUGGESTION`.
   - 4.2: no further changes found.
   
   ### Implementation notes
   
   - Spark: 
`sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/datetimeExpressions.scala`,
     `MakeTimestamp`, `MakeTimestampFromDateTime`, `TryMakeTimestamp`, 
`TryMakeTimestampFromDateTime`,
     `MakeTimestampExpressionBuilder`, `TryMakeTimestampExpressionBuilder`; 
`DateTimeUtils.makeTimestamp`
     
(`sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/DateTimeUtils.scala`).
   - DataFusion core has `make_date` and `make_time` but no `make_timestamp`, 
so write this from scratch.
   - Represent `TIMESTAMP` as `Timestamp(Microsecond, Some(tz))` and 
`TIMESTAMP_NTZ` as `Timestamp(Microsecond, None)`,
     as the other datetime functions here do. The session zone comes from 
`datafusion.execution.time_zone` and ANSI from
     `datafusion.execution.enable_ansi_mode`. The result type depends on 
`spark.sql.timestampType`, which has no
     DataFusion equivalent; one option is to keep `make_timestamp` LTZ-only and 
let callers pick
     `make_timestamp_ntz` (see the separate issue), sharing one kernel.
   - Reuse `SparkTimeZone` in `crates/spark/src/function/datetime/timezone.rs` 
for Spark's zone spellings, and add a
     local-to-instant helper with Java's gap (shift forward) / overlap (earlier 
offset) rules, shared with `convert_timezone`. Note its
     documented `chrono-tz` limit after 2099. Put the code in 
`crates/spark/src/function/datetime/`.
   - Tests: `crates/sqllogictest/test_files/datetime/make_timestamp.slt` exists 
but every query is commented out (ported
     placeholders). Uncomment and expand it, and add `try_make_timestamp.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