Parsing dates and timestamps

Dates, timestamps and strings

A DATE is a calendar day. A TIMESTAMP is an instant in time, shown in the session time zone. Strings that look like dates are just text: they sort correctly only in yyyy-MM-dd form, and you cannot add days to them. Parse once, early, and keep real date types.

Run the example

from pyspark.sql import functions as F
result = signups.select(
    "user",
    F.to_date("signup_raw", "dd/MM/yyyy").alias("signup_date"),
    F.to_timestamp("last_login").alias("login_ts"),
    F.date_format(F.to_timestamp("last_login"), "EEE HH:mm").alias("login_label"),
    F.datediff(F.to_date("last_login"), F.to_date("signup_raw", "dd/MM/yyyy")).alias("days_to_login"),
)

SELECT user,
       to_date(signup_raw, 'dd/MM/yyyy')        AS signup_date,
       to_timestamp(last_login)                 AS login_ts,
       date_format(last_login, 'EEE HH:mm')     AS login_label,
       datediff(to_date(last_login), to_date(signup_raw, 'dd/MM/yyyy')) AS days_to_login
FROM signups

Cy's signup is null: 2025-02-20 does not match dd/MM/yyyy. Di has no login, so everything computed from it is null.

Mixed formats in one column

Try each format in turn and take the first that parses. coalesce returns the first non-null:

signup_date = F.coalesce(
    F.to_date("signup_raw", "dd/MM/yyyy"),
    F.to_date("signup_raw", "yyyy-MM-dd"),
)

coalesce(to_date(signup_raw, 'dd/MM/yyyy'), to_date(signup_raw, 'yyyy-MM-dd'))

In Spark 4.0 with ANSI mode on, to_date raises an error for a string that does not match; use try_to_timestamp or try_to_date style functions where available, or set the pattern per source so mismatches cannot happen.

Pattern letters

Formatting for output

date_format(col, pattern) turns a date or timestamp into a string. Keep it for the last step (reports, file names, labels): once formatted, the column is text again. To group by month, prefer trunc(col, "month"), which stays a date and sorts correctly.

Epoch seconds and time zones

• unix_timestamp(col) returns seconds since 1970-01-01 UTC; from_unixtime(secs) and timestamp_seconds(secs) go back.
• Epoch milliseconds (common in Kafka and APIs) need dividing by 1000 first, or use timestamp_millis.
• Parsing a timestamp string with no offset interprets it in spark.sql.session.timeZone. Set it to UTC on every job so results do not depend on where the cluster runs.
• to_utc_timestamp and from_utc_timestamp convert between UTC and a named zone such as Asia/Kolkata.

Common mistakes