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]