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]

Reply via email to