The data engineer's guide to timestamps

Nothing in data engineering has a worse ratio of hours-lost to concepts-involved than time. The ideas are simple. The failure modes are silent, they only show up at boundaries, and they are usually discovered by someone in a different timezone.

Rule one: store UTC, always#

Store every timestamp in UTC, with an explicit timezone designator. Convert to local time only at the point of display.

from datetime import datetime, timezone

datetime.now(timezone.utc)      # correct
datetime.utcnow()               # naive — no tzinfo, a trap in Python
datetime.now()                  # local time of whatever machine ran this

datetime.utcnow() deserves a specific warning. It returns the correct UTC value with no timezone attached, so anything downstream treats it as local. Every arithmetic operation on it is a coin flip. It is deprecated in modern Python for exactly this reason; use datetime.now(timezone.utc).

Rule two: name the column so the unit is unambiguous#

created_at_utc, not created. order_date_local, not date. The name is your only defence against the next person's assumption, and there will be a next person.

Rule three: know which day you mean#

"Daily revenue" requires a timezone to be a well-defined concept. A sale at 23:30 in Los Angeles is on one day locally and the next day in UTC.

There is no universally right answer. There is only a decision, which must be written down:

• UTC days. Simple, consistent globally, and disagrees with what any local team means by "yesterday".
• Company-local days. Matches finance and matches intuition. Awkward as soon as you have offices in three timezones.
• Customer-local days. Right for customer-facing metrics like "orders per day per store". Expensive: every row needs its own timezone attached, and daily totals no longer line up on a single clock.

Pick one, put it in the metric definition, and put it in the column name.

Rule four: daylight saving time will find you#

Two facts about DST that break code:

Some local times do not exist. When clocks jump forward, 02:30 simply did not happen on that date. Parsing "2026-03-08 02:30" in a US timezone is an error, not a value.

Some local times happen twice. When clocks fall back, 01:30 occurs twice, an hour apart. A local timestamp in that window is genuinely ambiguous, and a naive parse silently picks one.

If you store UTC, neither problem exists in your data. They only appear when you accept local times as input — so validate at that boundary, and reject rather than guess.

Also: a day is not always 24 hours in local time. Any code that adds 86,400 seconds to get "tomorrow" is wrong twice a year. Add one calendar day using a timezone-aware library.

Rule five: event time and processing time are different columns#

• Event time — when the thing happened. ordered_at.
• Processing time — when your system saw it. _loaded_at.

A purchase at 23:58 that arrives at 00:07 because the phone was offline belongs to yesterday by event time and today by processing time. Both are defensible. Only one is what finance means.

Store both. Report on event time. Accept the consequence: yesterday's totals change slightly for a few days as late events land. That is the data being honest. Publish the restatement window — "figures firm after 72 hours" — rather than pretending it does not happen.

The gap between the two columns is also free instrumentation. Its distribution tells you your real ingestion lag, and a shift in that distribution is an early warning that something upstream broke.

Rule six: get the storage type right#

Databases differ, and the difference matters:

• Postgres timestamptz stores a UTC instant and converts on read. Almost always what you want.
• Postgres timestamp stores wall-clock with no zone. A trap.
• BigQuery TIMESTAMP is an absolute instant; DATETIME is wall-clock with no zone.
• Snowflake TIMESTAMP_TZ keeps the offset; TIMESTAMP_NTZ does not.

Choosing the naive variant "because we only operate in one country" is a decision that ages badly and is expensive to reverse once there are billions of rows.

Rule seven: beware Unix timestamps in the wrong unit#

Seconds, milliseconds, microseconds, and nanoseconds all appear in the wild, all as bare integers. A milliseconds value read as seconds lands you in the year 56,000 — obvious. A seconds value read as milliseconds lands you in January 1970 — also obvious. What is not obvious is a mixed column where one vendor sends one unit and another sends the other, which produces a bimodal distribution of dates that looks like a data quality problem rather than a units problem.

Check the magnitude on ingest. Ten digits is seconds; thirteen is milliseconds.

The checklist#

• Store UTC with an explicit zone; convert at display only.
• Name columns with the unit and zone.
• Write down which timezone defines "a day" for each metric.
• Never add 86,400 seconds to get tomorrow.
• Keep event time and processing time as separate columns.
• Validate timestamp magnitude on ingest.
• Publish your restatement window.

Every one of these came from an incident. Most of them came from more than one.