Apache Spark / Basic: typed relational foundations / Lesson 4

Filtering, time zones and deterministic order

Express a reporting interval and stable output without confusing event instants, wall clocks and partition order.

About 4 min readContent reviewed 2026-10-28

On this page

Lesson goals

Use typed half-open date and timestamp boundaries.
Explain session time zone, timestamp types and explicit sorting.

A daily report needs a clock convention

“October 1 energy” is incomplete until the cooperative names the business time zone and whether readings represent instants or local wall-clock values. A source timestamp without an offset cannot reveal its original zone by itself. Spark supports timestamp-with-local-time-zone and timestamp-without-time-zone semantics, and the default TIMESTAMP alias depends on configuration. Record the intended type and session time zone at the job boundary. Do not infer them from the laptop displaying a sample. A daylight-saving change can make a local calendar day differ from exactly twenty-four elapsed hours.

Use an exclusive upper boundary

The SQL fixture uses DATE values, so the filter selects October 1 and excludes October 2 without timestamp parsing ambiguity. >= start and < end compose adjacent reporting periods without double-counting the shared boundary. BETWEEN includes both endpoints, which is often wrong when tomorrow’s midnight is the upper timestamp. Avoid formatting event_time into a string merely to compare a date label; that mixes display with eligibility and can interfere with useful source pruning. Derive typed boundaries from the documented clock and keep them consistent across input, aggregates and output partition labels.

sql Illustrative technical example; logical results are reasoned, not engine-tested.

SELECT reading_id, reading_day
FROM VALUES ('r1', DATE '2026-10-01'), ('r2', DATE '2026-10-02')
AS readings(reading_id, reading_day)
WHERE reading_day >= DATE '2026-10-01'
 AND reading_day < DATE '2026-10-02'
ORDER BY reading_id;

Expected output

reading_id | reading_day
r1         | 2026-10-01

Only r1 qualifies. The example uses calendar dates and does not assert timestamp-zone behavior.

Make the temporal assumption observable

In an independent authorized PySpark session, spark.sql.session.timeZone can be inspected and deliberately set according to the job contract. The fragment below illustrates the intended UTC setting but is not executed. It does not repair source strings that were mislabeled as UTC. Parse with explicit formats and retain offsets or source-zone metadata where needed. A TIMESTAMP_NTZ value represents a local date-time without instant conversion semantics; comparing it to an instant-type value can introduce implicit interpretation. Keep types aligned and test conversion boundaries rather than hiding differences with a final display format.

python Illustrative technical example; logical results are reasoned, not engine-tested.

job_zone = 'UTC'
# Illustrative session configuration in an independent lab only:
spark.conf.set('spark.sql.session.timeZone', job_zone)
observed_zone = spark.conf.get('spark.sql.session.timeZone')

The intended observed value would be UTC. No session configuration is changed by this course.

Distributed rows have no implicit global order

A DataFrame does not promise input-file order. orderBy establishes an explicit global ordering for the result, while sortWithinPartitions orders only within each physical partition. If event_time ties, add a stable reading_id before limiting or comparing reports. A limit without a total order can return a different valid subset after repartitioning or a plan change. Null placement also needs an explicit policy when missing times remain eligible. Sorting a large result can require shuffle and memory work, so order only where the consumer needs it and never confuse a convenient preview with a durable publication contract.

Keep the input snapshot and temporal policy together

Two correct runs can disagree when source files change between them or a late reading is included in only one input snapshot. Record batch identity, accepted event interval, ingestion cutoff and time-zone settings with published summaries. Distinguish event time from received time when evaluating lateness. A local synthetic fixture should include exact boundaries, missing times and a zone transition if the deployment uses such a region. The course performs no Spark timestamp parsing or filesystem reads. Expected calendar results are reasoned, and any safe date arithmetic used in validation is labeled as a Python-only model.

Common errors and fixes

CANNOT_PARSE_TIMESTAMP

Why it happens: A value does not match the expected timestamp format under the active parsing policy.
How to fix it: Inspect source format, offset and configured parser before conversion.
Prevent it next time: Test boundary and malformed timestamps across release changes.

A day report includes tomorrow’s midnight

Why it happens: An inclusive upper bound was used for an adjacent interval.
How to fix it: Use a typed half-open interval in the agreed clock.
Prevent it next time: Include exact shared boundaries in acceptance fixtures.

A preview changes order after repartitioning

Why it happens: Physical row order was mistaken for a global sort contract.
How to fix it: Apply explicit total ordering where the consumer requires it.
Prevent it next time: Compare unordered data as sets unless order is specified.

Check your understanding

1. Does sortWithinPartitions guarantee that all early readings precede all late readings?

Show worked solution

No. It orders rows only inside each partition. Use a deliberate global ordering when the consumer needs one and include a unique tie-breaker. Sorting has distributed cost, so avoid using it merely to make an unordered equality test pass.

2. Why should an event-time report also record the ingestion cutoff?