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]