andygrove opened a new issue, #226:
URL: https://github.com/apache/datafusion-spark/issues/226
Part of #175 and apache/datafusion-comet#7300.
### What the function does
`from_unixtime(unix_time[, fmt])` takes a number of seconds since
`1970-01-01 00:00:00 UTC`, turns it into an
instant, and returns that instant as a `STRING` in the session time zone
(`spark.sql.session.timeZone`), formatted
with the Spark datetime pattern `fmt`. Without `fmt` the pattern is
`yyyy-MM-dd HH:mm:ss`. `unix_time` is declared
`BIGINT` with `ImplicitCastInputTypes`, so other numeric types are
implicitly cast to `BIGINT` first (`DECIMAL` and
`DOUBLE` lose their fraction; `STRING` goes through a string-to-`BIGINT`
cast). `fmt` is a `STRING` (literal or
column). The result is `STRING` and always nullable; it is NULL if either
argument is NULL. Since 1.5.0.
The formatter is the same one `date_format` uses
(`TimestampFormatterHelper`), applied to
`unix_time * 1_000_000` microseconds. Pattern semantics, validation errors
and zone rendering are identical to
`date_format`.
DataFusion core has a `from_unixtime(expr[, timezone])` with a different
contract. It returns a
`Timestamp(Second, tz)` value, not a string; its second argument is a time
zone, not a format; and it does not
consult a pattern. This crate needs to override the name.
### Examples
Session time zone `America/Los_Angeles` (from `ExpressionDescription` and
`DateExpressionsSuite`):
```sql
SELECT from_unixtime(0, 'yyyy-MM-dd HH:mm:ss');
-- 1969-12-31 16:00:00
SELECT from_unixtime(0);
-- 1969-12-31 16:00:00
SELECT from_unixtime(1000, 'yyyy-MM-dd HH:mm:ss');
-- 1969-12-31 16:16:40
SELECT from_unixtime(-1000, 'yyyy-MM-dd HH:mm:ss.SSS');
-- 1969-12-31 15:43:20.000
SELECT from_unixtime(1460098800, 'yyyy-MM-dd');
-- 2016-04-08
SELECT from_unixtime(NULL), from_unixtime(1000, NULL);
-- NULL NULL
SELECT from_unixtime('aa');
-- ANSI on: CAST_INVALID_INPUT ('aa' cannot be cast to "BIGINT"); ANSI off:
NULL
```
### Edge cases to test
- NULL in either argument; a NULL literal format (`from_unixtime(1000,
NULL)` gives NULL); a column format.
- Implicit casts of the first argument: `INT`, `DECIMAL(…)` and `DOUBLE`
(`1.9` becomes `1`, truncated toward zero
by the cast). `STRING` (`'1000'` is valid; `'aa'` gives
`CAST_INVALID_INPUT` with ANSI on and NULL with ANSI
off). Under ANSI, a `DOUBLE`/`DECIMAL` outside the `BIGINT` range raises
`CAST_OVERFLOW`.
- Overflow: Spark multiplies `seconds * 1000000L` with ordinary (wrapping)
`Long` arithmetic. Values with
`|seconds| > 9223372036854` therefore wrap silently, with no error even
under ANSI. For example
`from_unixtime(9223372036854775807)` formats micros `-1000000`, i.e.
`1969-12-31 15:59:59` in
`America/Los_Angeles`. A Rust kernel must use `wrapping_mul` to match, not
checked or saturating arithmetic.
- Large and negative years: `253402300800` (`10000-01-01 00:00:00 UTC`)
formats as `+10000-...` with `yyyy`, because
`y` is rewritten to the signed proleptic year `u`. Pre-epoch and BC values
print `-` years unless `G` is in the
pattern.
- Session time zone and DST: the same seconds value formats differently per
zone. Cover instants on both sides of a
transition (e.g. `America/Los_Angeles` 2019-03-10 and 2019-11-03), fixed
offsets (`+05:45`), and pre-1900 LMT
offsets with seconds (`XXXXX` gives `-07:52:58` in 1582 for
`America/Los_Angeles`).
- Pattern errors, exactly as for `date_format`: `Y w W u` give
`SparkUpgradeException`
`INCONSISTENT_BEHAVIOR_CROSS_VERSION.DATETIME_PATTERN_RECOGNITION`; `e c`
give `DATETIME_WEEK_BASED_PATTERN`;
`A B n N p` give `INVALID_DATETIME_PATTERN.ILLEGAL_CHARACTER`; 5x `G M L E
Q q` / 7x `y` give
`INVALID_DATETIME_PATTERN.LENGTH` or `DATETIME_PATTERN_RECOGNITION`;
unknown letters give
`INVALID_DATETIME_PATTERN.WITH_SUGGESTION`.
- Fraction digits: the input is whole seconds, so `S` fields are always
zeros.
- `spark.sql.legacy.timeParserPolicy=LEGACY` uses `SimpleDateFormat`.
Reasonable to report as unsupported.
### Differences across Spark versions
- No change in evaluation from 3.4 to 4.2.
- The same pattern-error class changes as `date_format`: 3.x raised a plain
`IllegalArgumentException` for illegal
letters and lengths; 4.0 introduced `INVALID_DATETIME_PATTERN.*` /
`DATETIME_WEEK_BASED_PATTERN`; 4.1 classified
the remaining unknown-letter case as
`INVALID_DATETIME_PATTERN.WITH_SUGGESTION`.
- ANSI is on by default from 4.0, which changes the implicit
`STRING`/`DOUBLE` to `BIGINT` casts from NULL to errors.
- 4.0 widened `fmt` to collated strings.
### Implementation notes
- Spark:
`sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/datetimeExpressions.scala`,
`FromUnixTime`. Formatting is in
`sql/api/src/main/scala/org/apache/spark/sql/catalyst/util/TimestampFormatter.scala`.
- DataFusion core `from_unixtime`
(`datafusion/functions/src/datetime/from_unixtime.rs`) cannot be reused: it
returns
a timestamp, not a string. The equivalent is
`date_format(timestamp_seconds(x), fmt)` with wrapping multiplication.
- Build on the Spark pattern compiler proposed for `date_format`, so both
use one formatting kernel. Put it in
`crates/spark/src/function/datetime/`. The default-pattern case
(`yyyy-MM-dd HH:mm:ss`) is a good first native
path. The other literal formats can be supported when they fall in the
verified subset described in the
`date_format` issue; outside that subset, return an "unsupported pattern"
error so callers can fall back.
- Resolve the session zone with `SparkTimeZone`
(`crates/spark/src/function/datetime/timezone.rs`) from
`datafusion.execution.time_zone`.
- Tests: there is no `from_unixtime.slt` yet. Add
`crates/sqllogictest/test_files/datetime/from_unixtime.slt`
following existing files, including at least two session time zones.
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]