harsh-ande opened a new issue, #26128:
URL: https://github.com/apache/datafusion/issues/26128
### Describe the bug
The Spark-compatible `unix_millis` and `unix_seconds` functions in
`datafusion-spark` return different results from Spark for timestamps before
`1970-01-01` that have a sub-unit component. Spark floors the result;
DataFusion truncates toward zero.
For example, one microsecond before the epoch gives `unix_millis = -1` in
Spark but `0` in DataFusion.
`unix_micros` is not affected, since it does no unit conversion.
### To Reproduce
DataFusion (`datafusion-cli --spark`):
```sql
SELECT
unix_millis(arrow_cast(-1, 'Timestamp(Microsecond, None)')) AS
millis_a,
unix_millis(arrow_cast(-1001, 'Timestamp(Microsecond, None)')) AS
millis_b,
unix_seconds(arrow_cast(-1, 'Timestamp(Microsecond, None)')) AS
secs_a,
unix_seconds(arrow_cast(-1000001, 'Timestamp(Microsecond, None)')) AS
secs_b;
```
Spark 4.0.0 (`spark.sql.session.timeZone=UTC`):
```sql
SELECT
unix_millis(timestamp_micros(-1)),
unix_millis(timestamp_micros(-1001)),
unix_seconds(timestamp_micros(-1)),
unix_seconds(timestamp_micros(-1000001));
```
```
| input (µs) | function | Spark 4.0.0 | DataFusion |
|-----------:|----------------|------------:|-----------:|
| -1 | `unix_millis` | -1 | 0 |
| -1001 | `unix_millis` | -2 | -1 |
| -1 | `unix_seconds` | -1 | 0 |
| -1000001 | `unix_seconds` | -2 | -1 |
```
Exact multiples of the unit (e.g. `-1000` µs → `-1` ms) and non-negative
values (e.g. `1500` µs → `1` ms) already match
### Expected behavior
Match Spark: floor-divide the microsecond value by 1,000 for `unix_millis`
and by 1,000,000 for `unix_seconds`.
### Additional context
The functions were added in #19892. `SparkUnixTimestamp::simplify` rewrites
the call as `CAST(CAST(ts AS Timestamp(<unit>, "UTC")) AS Int64)`. The Arrow
cast to a coarser timestamp unit truncates toward zero, which is wrong for
negative values with a remainder.
Spark computes these in `TimestampToLongBase` (`datetimeExpressions.scala`)
using `Math.floorDiv`.
I searched existing issues and PRs and didn't find this reported. I have a
fix with sqllogictest coverage and will open a PR shortly.
--
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]
