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

```text
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:

```python
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".

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

```python
# 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.

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

```python
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.

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

```python
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.

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

```python
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/pst_tqCCxNmTqoaYksbrf_aoAg