apache / apache/datafusion

bug: datafusion-spark format_string %t timestamp specifiers do not match Spark behavior

Open
#21,515 3 comments 0 reactions 1 assignee Claimed by @kumarUjjawal View on GitHub
spark
Dominant language
Rust
Stars
9.3k
Forks
2.4k
Avg merge
3d 7h
Merged PRs (30d)
344

Description

### Describe the bug

The `datafusion-spark` `format_string` implementation produces different results from Apache Spark for `%t` timestamp format specifiers (`%tH`, `%tM`, `%tS`, `%tY`, etc.).

Spark passes the raw timestamp microseconds to Java's `Formatter`, which interprets them as milliseconds. This is a known Spark quirk, but `datafusion-spark` should match Spark's behavior for compatibility.

### To Reproduce

**PySpark (Spark behavior):**
```sql
SELECT format_string('Hour: %tH', TIMESTAMP '2023-12-25 14:30:45');
-- Hour: 10

SELECT format_string('Year: %tY', TIMESTAMP '2023-12-25 14:30:45');
-- Year: 55952

SELECT format_string('Second: %tS', TIMESTAMP '2023-12-25 14:30:45');
-- Second: 00
```

Spark stores timestamps as microseconds since epoch but Java's `Formatter` interprets the value as milliseconds, producing these (surprising but consistent) results.

**DataFusion-spark (current behavior):**

The `.slt` tests at `string/format_string.slt` expect "correct" results like `Hour: 14`, `Year: 2023`, suggesting the DataFusion-spark implementation handles the timestamp properly rather than matching Spark's behavior.

### Expected behavior

`datafusion-spark` should match Spark's output for `%t` format specifiers, even though Spark's behavior is arguably a bug. Spark compatibility is the goal.

| Expression | Spark | datafusion-spark should match |
|---|---|---|
| `format_string('Hour: %tH', TIMESTAMP '2023-12-25 14:30:45')` | `Hour: 10` | `Hour: 10` |
| `format_string('Year: %tY', TIMESTAMP '2023-12-25 14:30:45')` | `Year: 55952` | `Year: 55952` |
| `format_string('Second: %tS', TIMESTAMP '2023-12-25 14:30:45')` | `Second: 00` | `Second: 00` |

### Additional context

**Root cause in Spark:** Spark stores timestamps internally as microseconds since epoch. When `format_string` encounters a `%t` specifier, it passes the raw microsecond long value to Java's `java.util.Formatter`, which interprets it as milliseconds since epoch. This 1000x mismatch produces wrong-looking but consistent results.

The `.slt` tests in `string/format_string.slt` (lines 356-579) have ~33 expected values that need to be updated to match Spark's actual output. The expected values appear to have been generated by DataFusion's implementation rather than verified against Spark.

This was discovered by running a PySpark validation script against the `.slt` test files (see #17045, #21508).

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.