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

   Part of #175 and apache/datafusion-comet#7300.
   
   ### What the function does
   
   `timestamp_seconds(seconds)` returns the `TIMESTAMP` that is `seconds` 
seconds after `1970-01-01 00:00:00 UTC`.
   `seconds` may be any numeric type (`TINYINT`, `SMALLINT`, `INT`, `BIGINT`, 
`DECIMAL`, `FLOAT`, `DOUBLE`); the expression
   uses `ExpectsInputTypes` with `NumericType`, so non-numeric input (e.g. 
`STRING`) is an analysis error rather than being
   cast. Fractional seconds are kept to microsecond precision.
   
   The result is always `TIMESTAMP` (LTZ), independent of 
`spark.sql.timestampType`. The stored value does not depend on
   the session time zone; only its display does. NULL input gives NULL. For 
`FLOAT`/`DOUBLE` input the result is always
   nullable because NaN and +/-Infinity give NULL. Since 3.1.0; the inverse is 
`unix_seconds` (already in this crate).
   
   ### Examples
   
   Session time zone `America/Los_Angeles` (as in Spark's docs and golden 
files):
   
   ```sql
   SELECT timestamp_seconds(1230219000);
   -- 2008-12-25 07:30:00
   SELECT timestamp_seconds(1230219000.123);
   -- 2008-12-25 07:30:00.123
   SELECT timestamp_seconds(-1230219000);
   -- 1931-01-07 00:30:00
   SELECT timestamp_seconds(null);
   -- NULL
   SELECT timestamp_seconds(1.23), timestamp_seconds(1.23d), 
timestamp_seconds(FLOAT(1.23));
   -- 1969-12-31 16:00:01.23   1969-12-31 16:00:01.23   1969-12-31 16:00:01.23
   SELECT timestamp_seconds(0.1234567d), timestamp_seconds(FLOAT(0.1234567));
   -- 1969-12-31 16:00:00.123456   1969-12-31 16:00:00.123456
   SELECT timestamp_seconds(0.1234567);
   -- java.lang.ArithmeticException: Rounding necessary
   SELECT timestamp_seconds(1230219000123123);
   -- java.lang.ArithmeticException: long overflow
   ```
   
   ### Edge cases to test
   
   - Integral input: `seconds * 1_000_000` with `Math.multiplyExact`; overflow 
raises `ArithmeticException`
     ("long overflow") whether or not ANSI is enabled (same output in Spark's 
ANSI and non-ANSI golden files). Test
     `i64::MAX / 1_000_000` and one past it, positive and negative.
   - `DECIMAL` input: exact `BigDecimal` multiply then `longValueExact()`, so 
more than 6 fractional digits is an error
     ("Rounding necessary") and a too-large value is an error ("Overflow"), 
both regardless of ANSI. Test
     `DECIMAL(38, 0)` values and negative decimals such as `-1.5`.
   - `FLOAT` / `DOUBLE` input: `(d * 1e6).toLong`, which truncates toward zero 
(`-0.0000015d` gives `-1` micro, not `-2`)
     and saturates to `i64::MIN`/`i64::MAX` instead of failing for huge values 
(`1e300d`). `FLOAT` is widened to double
     first, so `FLOAT(1.23)` keeps its float rounding error before truncation. 
`-0.0` gives the epoch.
   - NaN, `Infinity`, `-Infinity` (float and double) give NULL.
   - `TINYINT` / `SMALLINT` / `INT` / `BIGINT` all work; `STRING`, `BOOLEAN`, 
`TIMESTAMP` input fail analysis with
     `DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE`.
   - Negative values before 1970, and values far outside 0001..9999 (allowed; 
any `i64` microsecond value is a valid
     Spark timestamp).
   - Display depends on `spark.sql.session.timeZone`, including DST: 
`timestamp_seconds(1552212000)` (2019-03-10 10:00 UTC)
     in `America/Los_Angeles`. The stored value must be the same for every zone.
   - Literal vs column input; dictionary-encoded input.
   
   ### Differences across Spark versions
   
   None found (3.4 through 4.2 have the same evaluation; only the internal 
`NullIntolerant` trait was refactored).
   ANSI mode does not affect this function.
   
   ### Implementation notes
   
   - Spark: 
`sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/datetimeExpressions.scala`,
     `SecondsToTimestamp`.
   - DataFusion core `to_timestamp_seconds` is not a match: it returns 
`Timestamp(Second, tz)` (no sub-second precision),
     casts `FLOAT`/`DOUBLE` to `Int64` first (so `1.23` loses `.23`), also 
parses strings, and does not raise Spark's
     overflow / rounding errors. Write a dedicated kernel returning 
`Timestamp(Microsecond, Some(tz))` with the session zone
     from `datafusion.execution.time_zone`, matching how this crate types Spark 
`TIMESTAMP`.
   - Put it in `crates/spark/src/function/datetime/` next to `unix.rs` (the 
inverse functions); `timestamp_millis` and
     `timestamp_micros` (separate issues) can share the integral path.
   - Tests: `crates/sqllogictest/test_files/datetime/timestamp_seconds.slt` 
exists but every query is commented out
     (ported placeholders). Uncomment and expand it. 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