The guiding principle (the motto)

Keep UTC for as long as possible.
Change to a local timezone only when it is truly required.

This principle was not theoretical.
It emerged from a real incident — one that almost became a production bug, and only didn’t because of a fortunate configuration coincidence.

Why time zones are dangerous in ETL

Time-related issues are among the hardest problems in data engineering because:

  • pipelines usually don’t fail
  • data looks “reasonable” when inspected manually
  • errors appear only during part of the year (DST)
  • different tools show different values for the same data

This post tells the story of one such case and extracts the lessons that matter.

The situation we were in

We had an ETL pipeline with several stages:

  • a source system exposing timestamps as timestamp without time zone
  • a silver layer storing data as timestamptz
  • a gold layer also using timestamptz
  • a Pentaho pipeline performing timezone transformations
  • an external API expecting timestamps in UTC (ISO 8601 with Z)

Everything seemed consistent.

Even better, all validations during winter showed no issues at all.

What the source guaranteed (and what it didn’t)

The source system guaranteed the following:

“All timestamps are in UTC.”

However:

  • the column type was timestamp (no timezone)
  • the semantic meaning (UTC) was purely contractual

This is very common — and also very dangerous if handled implicitly.

What the pipeline was doing

Conceptually, the pipeline did this:

  1. Read a timestamp that represents UTC
  2. Convert it to UTC
  3. Convert it to Europe/Lisbon
  4. Store the result as timestamptz in the silver layer
  5. Propagate the same value to the gold layer
  6. Send the value to the API

At first glance, this seems fine.

In reality, it only worked because of luck.

The lucky coincidence (and why things “worked”)

Here is the critical part of the story.

  • The Silver database server was configured with timezone Europe/Lisbon
  • The Gold database server was also Europe/Lisbon
  • The Pentaho runtime session was aligned with Europe/Lisbon
  • The pipeline explicitly converted:
    • first from UTC
    • then to Europe/Lisbon

Because of this alignment:

  • Lisbon wall time was interpreted correctly
  • the cast to timestamptz produced the correct instant
  • clients in Lisbon saw the expected local hour
  • clients in UTC saw the expected UTC hour

In other words:
the system was correct by coincidence, not by design.

Why this was fragile

The pipeline was correct because the runtime and database session timezones were aligned with the intention of the transformation.

In practice, that meant:

  • the ETL step that produced a Lisbon wall time was interpreted consistently as Europe/Lisbon
  • the value was stored as a timestamptz representing the correct instant
  • different clients (UTC vs Europe/Lisbon) only changed the display, not the stored instant

So the fragile part was not “someone running the pipeline from a different machine” in the abstract.
The fragile part was this:

Any implicit conversion that depends on the session timezone becomes unsafe if the session timezone changes.

This can happen when:

  • the ETL tool starts new connections with a different TimeZone setting
  • a driver defaults differently in another environment
  • a connection pool changes session parameters
  • a DBA changes database defaults and the ETL does not explicitly set TimeZone

What would actually break it

Only the parts of the pipeline that rely on implicit interpretation would break — for example:

  • casting a timestamp (no timezone) to timestamptz without explicitly stating which timezone that timestamp represents

If the ETL always uses explicit conversions like:

  • ... AT TIME ZONE 'UTC'
  • ... AT TIME ZONE 'Europe/Lisbon'

then the result is deterministic and does not depend on where it runs.

The real conclusion

This is robust if and only if:

  • the pipeline explicitly sets or controls the session timezone, or
  • it avoids implicit casts and always anchors semantics with AT TIME ZONE

The important lesson is not “don’t move servers”, it’s this:

Don’t let correctness depend on implicit session timezone behavior. Make conversions explicit.

How the problem was discovered

The issue surfaced when validating summer (DST) data.

In July:

  • Lisbon is UTC+1
  • UTC and local time diverge

We noticed that:

  • values stored in the gold layer looked correct locally
  • but values sent to the API were off by one hour

No errors.
No warnings.
Just subtly wrong data.

DST exposed what winter had hidden.

The missing step

The key realization was simple:

  • internally, the system was working with Lisbon-local instants
  • the API, however, explicitly required UTC

What was missing was a final, explicit conversion back to UTC before sending data to the API.

Once that step was added:

ts AT TIME ZONE 'UTC'

right before serialization, the values sent to the API became correct.

Why timestamptz didn’t save us

A common misconception is:

“If we use timestamptz, timezones are handled.”

timestamptz:

  • stores an instant
  • does not store a timezone
  • displays values using the client/session timezone

If the instant is wrong, timestamptz only makes the problem harder to see.

The tooling problem made it worse

Another major source of confusion was client tooling.

Different tools showed different values for the same row:

  • ETL runtime
  • database server
  • DataGrip / DBeaver
  • developer laptops

Each had its own timezone configuration.

This led to questions like:

  • “Which value is correct?”
  • “Did the pipeline shift the data?”
  • “Is the database wrong?”

In reality:

  • the data was the same
  • only the representation differed

Without explicitly checking session and server timezones, debugging becomes guesswork.

The corrected mental model

We ended up with a clear separation:

1. Instants (system truth)

  • stored as timestamptz
  • normalized to UTC
  • used for joins, ordering, comparisons, integrations

2. Representations (human view)

  • derived from instants
  • converted to local timezones
  • never persisted as the primary truth

The corrected approach

Normalize early

If a source provides a UTC-semantic timestamp:

ts AT TIME ZONE 'UTC'

Do this once. Do it explicitly. Do it as early as possible.

Keep UTC internally

Persist only instants. Avoid storing local wall time as truth.

Convert at the edges

Convert only:

  • for UI
  • for reports
  • for external systems with explicit requirements

Serialize explicitly for APIs

If an API requires ISO 8601 UTC:

to_char(ts AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS"Z"')

Never rely on implicit session settings.

The final lesson

This incident taught us something important:

The most dangerous bugs are the ones that work “by luck”.

The system behaved correctly:

  • not because it was well designed
  • but because all environments happened to align

That is not a guarantee — it is a risk.

Final takeaway

Timezones are not a formatting detail. They are part of your data model and system architecture.

And the principle that remains is simple:

Keep UTC for as long as possible. Convert only when you truly need to. Never rely on luck.

Because summer always comes.