Skip to content

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

Description

@andygrove

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):

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).

Activity

  1. shruti2522 commented on Apr 10, 2026

    @shruti2522
    Contributor

    take

  2. kumarUjjawal commented on Jun 7, 2026

    @kumarUjjawal
    Contributor

    Hi @shruti2522 I have unassigned to free up the issue, if you're still interested in working on this please let me know.

  3. self-assigned this
    on Jun 8, 2026
  4. kumarUjjawal commented on Jun 8, 2026

    @kumarUjjawal
    Contributor

    I wanted to share my findings

    As per the issue:
    Spark keeps timestamps as microseconds, but hands that raw number to Java's formatter, which reads it as milliseconds. So every %t value comes out 1000x off, which is why a normal 2023 date prints as the year 55952.

    The problem is what that 1000x does downstream. Those inflated values land tens of thousands (and in some cases millions) of years in the future, which is well outside the range the date library we normally use can handle. So to reproduce Spark's output faithfully, we would end up having to write our own date math and our own timezone/daylight-saving handling from
    scratch.

    That leaves me unsure this is worth the maintenance cost, so I wanted to check the direction.

    1. Leave the current (correct-looking) output as-is and just document that we intentionally differ from Spark on this one quirk.
    2. Match the quirk only for the common UTC case, which is far less code, and document that unusual timezones at extreme dates may not match.
    3. Go for full fidelity, which is the large change

    Worth noting this only affects %t timestamp specifiers, the rest of format_string already works.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

Type

No type

Projects

No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions