[FAQ] Spark Error When Casting TIMESTAMP_NTZ to BIGINT

[mlpub]

Course

data-engineering-zoomcamp

Question

Why does this query fail?

SELECT CAST(tpep_pickup_datetime AS BIGINT)
FROM yellow_2025_11

Error "cannot cast TIMESTAMP_NTZ to BIGINT :

AnalysisException: [DATATYPE_MISMATCH.CAST_WITHOUT_SUGGESTION] Cannot resolve "CAST(tpep_pickup_datetime AS BIGINT)" due to data type mismatch: cannot cast "TIMESTAMP_NTZ" to "BIGINT". SQLSTATE: 42K09; line 3 pos 4;

Answer

Because the column type is TIMESTAMP_NTZ (timestamp without timezone). Spark does not allow direct casting from TIMESTAMP_NTZ to numeric types like BIGINT.

Use to_unix_timestamp functions to convert:

SELECT to_unix_timestamp(tpep_pickup_datetime)
FROM yellow_2025_11

Checklist

• I have searched existing FAQs and this question is not already answered
• The answer provides accurate, helpful information
• I have included any relevant code examples or links

[github-actions]

✅ FAQ NEW proposal created in PR #241

Please review and approve the changes.