8.2 Dates, Unix Epoch, and Time-Zone Conversions

Start with the input type and unit

A DateType value represents a calendar date without a time-of-day or time-zone. A timestamp represents a time value whose interpretation depends on the timestamp type and session configuration; TimestampType normally describes an instant rendered in the session time zone. TimestampNTZType represents a local date-time without a time zone. A string such as "2026-10-16" is text until parsed or cast. Formatting a timestamp produces text, not a date-typed column.

Unix epoch is a numeric count from the UTC epoch. APIs commonly send seconds or milliseconds, and confusing them produces dramatically wrong dates. A ten-digit-looking example is not enough proof of units; read the data contract. Do not divide an already second-valued timestamp by 1000, or pass milliseconds unchanged to a seconds function. Precision and overflow also depend on the chosen numeric type.

Seconds to a date string

from pyspark.sql import functions as F
spark.conf.set("spark.sql.session.timeZone", "UTC")
raw = spark.createDataFrame([(0,), (86400,)], "epoch_seconds LONG")
converted = raw.select(
    "epoch_seconds",
    F.from_unixtime("epoch_seconds", "yyyy-MM-dd HH:mm:ss").alias("utc_text"),
    F.to_date(F.from_unixtime("epoch_seconds")).alias("event_date")
)
converted.orderBy("epoch_seconds").show()
# 0 -> 1970-01-01 00:00:00 and date 1970-01-01
# 86400 -> 1970-01-02 00:00:00 and date 1970-01-02

There are 24 × 60 × 60 = 86,400 seconds in the illustrated UTC day. from_unixtime returns a string using the session zone, so the explicit UTC setting is part of the example. A different zone may show a prior calendar date for epoch zero. The epoch still describes the same instant; the displayed calendar fields differ. This is why a quiz must state the zone when asking for a precise rendered date.

Milliseconds and timestamps

Spark 3.5 offers timestamp_seconds, timestamp_millis and timestamp_micros to create timestamps from specified epoch units. Choose the one matching the input contract.

raw_ms = spark.createDataFrame([(1000,)], "epoch_ms LONG")
typed = raw_ms.select(
    F.timestamp_millis("epoch_ms").alias("event_ts")
)
# In UTC, 1000 milliseconds -> 1970-01-01 00:00:01

Dividing integer milliseconds by 1000 before from_unixtime can lose fractional precision if the calculation or cast truncates. For second-resolution output that may be intentional; for subsecond event ordering it may be wrong. Avoid converting repeatedly between timestamp and strings when the subsequent calculation needs timestamp semantics. Keep typed values for joins, filters and date/time calculations.

Parse with an explicit pattern

text_dates = spark.createDataFrame(
    [("16/10/2026", "2026-10-16 09:30:00")],
    "calendar_text STRING, time_text STRING"
)
parsed = text_dates.select(
    F.to_date("calendar_text", "dd/MM/yyyy").alias("day"),
    F.to_timestamp("time_text", "yyyy-MM-dd HH:mm:ss").alias("ts")
)

Pattern letters are case-sensitive: MM is month while mm is minute. The displayed pattern must match the data order. Do not guess whether 03/04/2026 means March 4 or April 3 from locale assumptions. State the expected pattern, then measure failed parses. A syntactically valid date can still violate a business range, such as a delivery date before an order date.

Invalid-input behavior varies with ANSI mode and parser policy. In configurations that raise on malformed input, an action can fail instead of yielding null. Spark 3.5 has try_to_timestamp for tolerant timestamp parsing; inspect the installed API when choosing a tolerant function. Explicitly distinguish "we reject invalid input" from "we keep it with a null parsed value" and preserve raw text when diagnostics require it.

Calendar components and arithmetic

For example, datediff(to_date(lit("2026-10-16")), to_date(lit("2026-10-13"))) returns 3. Reversing the arguments returns -3. Adding a calendar month is not the same as adding thirty days: month lengths vary, and end-of-month rules must be considered. A midnight boundary computed in a local zone is not necessarily a fixed UTC-hour offset through daylight-saving changes.

A robust event pipeline

Suppose an API supplies event_ms and a user-facing local display date. Parse event_ms into a timestamp using its documented unit, retain the original numeric value for traceability, and derive a reporting date in the required zone. Validate missing values, unit ranges and impossible future/past values before aggregation. Two users can see different local dates for the same instant; decide whether the report groups by UTC day, business-location day or user-local day.

Use date functions rather than hard-coded substrings when the source is already typed. If a requirement asks for a date string, format deliberately; if it asks for DateType, use to_date and inspect the schema. See date/time function APIs and datetime patterns.