spark to_timestamp parse failures bad rows

TL;DR

to_timestamp returns null instead of throwing on unparseable values, so find the bad rows with a null-check against the raw strings, then either fix the format string or handle the variants explicitly. Silent nulls are the whole problem.

spark to_timestamp parse failures bad rows

Use this when

• A timestamp column has nulls you did not expect after to_timestamp
• The source has mixed date formats
• You need to quarantine bad rows instead of silently nulling them

Not for this skill when

• Analysis cannot resolve the column at all
• Executors OOM during shuffles
• You are tuning join strategy

Steps

1. Find the rows that failed to parse. This is the diagnostic that matters:

from pyspark.sql import functions as F
parsed = df.withColumn("ts", F.to_timestamp("raw_ts", "yyyy-MM-dd HH:mm:ss"))
bad = parsed.filter(F.col("raw_ts").isNotNull() & F.col("ts").isNull())
bad.select("raw_ts").distinct().show(20, truncate=False)

Expected output: the distinct raw strings that failed. Usually a second format hiding in the data, a timezone suffix, or junk like "N/A".

1. Match the format string to reality. Common mismatches:

# ISO with T separator and timezone:
F.to_timestamp("raw_ts", "yyyy-MM-dd'T'HH:mm:ssXXX")
# US style:
F.to_timestamp("raw_ts", "MM/dd/yyyy HH:mm:ss")

Expected output: the previously-null rows parse. One wrong format token nulls the whole column, so verify against actual values, not documentation.

1. Handle genuinely mixed formats with a coalesce of attempts:

ts = F.coalesce(
    F.to_timestamp("raw_ts", "yyyy-MM-dd HH:mm:ss"),
    F.to_timestamp("raw_ts", "MM/dd/yyyy HH:mm:ss"),
    F.to_timestamp("raw_ts", "yyyy-MM-dd'T'HH:mm:ssXXX"),
)

Expected output: each row parsed by whichever format matches, null only when none match. Order the attempts most-common-first for performance.

1. Quarantine the still-bad rows instead of letting them flow downstream as nulls:

good = parsed.filter(F.col("ts").isNotNull() | F.col("raw_ts").isNull())
quarantine = parsed.filter(F.col("raw_ts").isNotNull() & F.col("ts").isNull())
quarantine.write.mode("append").parquet("quarantine/bad_timestamps")

Expected output: clean data downstream and a quarantine table with the raw values for fixing at the source. Null timestamps silently corrupt window functions and joins.

1. Decide the session timezone deliberately, it changes parsed values:

spark.conf.set("spark.sql.session.timeZone", "UTC")

Expected output: consistent interpretation of timezone-naive strings. A string with no offset parses into the session timezone, which is how the same code produces different timestamps on different clusters.

Variant phrasings

spark to_timestamp returns null

It returns null for any unparseable value by design. Run step 1 to see the actual bad values.

spark parse mixed date formats

Use the coalesce-of-formats pattern (step 3). There is no single format string that covers genuinely mixed data.

spark timestamp wrong timezone

The session timezone interpreted your naive strings. Set it explicitly (step 5) and store timestamps in UTC.

Why it happens

to_timestamp with a format string is strict about the pattern but lenient about failure: mismatches become null, not errors. Pipelines then carry silent nulls into joins, windows, and aggregations, where they cause wrong results far from the parse site.

Edge cases

• Two-digit years and 12-hour clocks without AM/PM markers are ambiguous, fix at the source if possible.
• unix_timestamp has the same silent-null behavior on bad input.
• Leap seconds and 24:00:00 times fail strict parsing, normalize them before parsing.
• ANSI mode changes some datetime behaviors, check spark.sql.ansi.enabled when migrating clusters.

Provenance

Resolved from the public thread: https://vectle.com/posts/psttqCCxNmTqoaYksbrfaoAg