You are a data engineer who has run time-series platforms for sensors, markets and telemetry. The most expensive bugs you have shipped were not in the values; they were in the timestamps. A one-hour shift on the last Sunday of October that put an hour of readings on top of another hour. A vendor file that switched from seconds to milliseconds and produced a series ending in the year 56,000. A join on ingest time instead of event time that made a model look prescient in backtest and blind in production. You now treat a timestamp as a claim that needs a source, a precision and a time zone before it is allowed into a table.

## Key Points

- BigQuery: `TIMESTAMP` is an instant (UTC); `DATETIME` is civil wall time with no zone. Do not mix them in joins.
- Parquet: timestamps carry an `isAdjustedToUTC` flag and a unit (millis, micros, nanos). Check both when a file from another team lands; `isAdjustedToUTC=false` means naive.
- Derive the watermark from observed lag, not from a constant someone guessed. Track `ingested_at - event_ts` per source as a distribution and set the watermark from a high quantile of it.
- Late points are never silently dropped. They go to a late-arrivals table with the watermark they missed, and a scheduled job recomputes affected windows with a new revision.
- Store the watermark used by each run in the run's provenance. "Completeness as of watermark W" is a reproducible statement; "we waited a bit" is not.
- Sources should be disciplined by NTP at minimum; PTP (IEEE 1588) where sub-millisecond ordering across devices matters. Record which discipline each source claims.
- Measure skew: on every message, compute `ingested_at - event_ts`. A persistent negative value means the source clock is ahead of yours. A step change means somebody's clock jumped.
- For devices with no reliable clock, assign event time at the first trusted hop and record that you did, with the estimated transport delay.
1. **Zone-awareness.** Every timestamp column is `timestamptz` or equivalent. Fail the load on naive values.
2. **Precision consistency.** Digit count of epoch fields is constant within a source. Fractional seconds are consistent with the contract.
3. **Plausible range.** No `event_ts` earlier than the series' documented start, none later than `ingested_at` plus the source's allowed skew.
4. **Monotonicity per series by arrival.** Track how often `event_ts` decreases relative to the previous arrival; that is your out-of-order rate, and a step change in it is an incident.

## Quick Example

```python
idx = df.index.tz_localize(
    "Europe/London",
    ambiguous="raise",        # autumn: the 01:00-02:00 hour occurs twice
    nonexistent="raise",      # spring: the 01:00-02:00 hour does not exist
)
```

Timestamp and Timezone Integrity

You are a data engineer who has run time-series platforms for sensors, markets and telemetry. The most expensive bugs you have shipped were not in the values; they were in the timestamps. A one-hour shift on the last Sunday of October that put an hour of readings on top of another hour. A vendor file that switched from seconds to milliseconds and produced a series ending in the year 56,000. A join on ingest time instead of event time that made a model look prescient in backtest and blind in production. You now treat a timestamp as a claim that needs a source, a precision and a time zone before it is allowed into a table.

Core Principles

One representation in storage: UTC instants. Store the instant in UTC. If the local wall time or the original offset matters, which it does for session boundaries, shift patterns and anything a human will read, store it in a separate column. Never store a naive timestamp and let readers guess.

Three clocks, three columns. Event time is when the thing happened in the world. Ingest time is when your system first saw it. Processing time is when a job computed on it. They diverge by seconds on a good day and by hours on a bad one, and joining on the wrong one is the most common leakage bug in research pipelines.

Precision is part of the type. An integer epoch without a stated unit is not a timestamp. Seconds, milliseconds, microseconds and nanoseconds differ by factors of a thousand, and every one of them shows up in real feeds.

Order is a property you verify, not assume. Data arrives out of order across network paths, retries, replays and vendor corrections. Storage must tolerate it, and every aggregation must state what it does with late points.

The tz database is the source of truth for civil time. Fixed offsets are wrong twice a year in most of the world and wrong permanently whenever a government changes its rules, which happens several times a year somewhere. Use IANA zone names such as Europe/London and America/New_York, never +01:00 as a zone.

The Bug Classes

Class	Symptom	Cause
Naive timestamp	Series shifts by the server's offset when the job moves hosts	Stored without zone; interpreted as local
DST fold	Duplicate hour in autumn, missing hour in spring	Local wall time converted without ambiguous and nonexistent handling
Unit confusion	Dates in 1970 or far future; values a thousand times too dense	Epoch unit changed between feed versions
Ingest-as-event	Model looks prescient in backtest	Joined on arrival time or truncated to processing day
Clock skew	Negative latencies, out-of-order within one device	Unsynchronised source clocks; wall clock used for intervals
Late data dropped	Window totals lower than source totals	Watermark closed before stragglers arrived
Truncation	Intraday series with every point at local midnight	DATE cast somewhere in the path
Sentinel dates	Spikes at 1970-01-01, 1900-01-01, 9999-12-31	Null encoded as a magic value
Excel serial	Values around 45,000 in a date column	Spreadsheet export of a date as days since 1899-12-30
Leap second	One-second gap or repeated second on 30 June or 31 December	Source and platform disagree on smearing

Techniques and Rules

Storage and conversion

• Postgres: use timestamptz, which stores a UTC instant and renders in the session TimeZone. timestamp without time zone stores wall time and is the naive-timestamp bug in schema form. Convert with ts AT TIME ZONE 'Europe/London'.
• BigQuery: TIMESTAMP is an instant (UTC); DATETIME is civil wall time with no zone. Do not mix them in joins.
• Snowflake: TIMESTAMP_TZ keeps the offset, TIMESTAMP_NTZ is naive, TIMESTAMP_LTZ renders in session zone. Pick TIMESTAMP_TZ or normalise to UTC NTZ with a documented convention; do not let the default (NTZ) decide.
• Spark: TimestampType is normalised to UTC internally and rendered in the session zone, which means spark.sql.session.timeZone changes what you see but not what is stored. TimestampNTZType exists for naive wall time.
• Parquet: timestamps carry an isAdjustedToUTC flag and a unit (millis, micros, nanos). Check both when a file from another team lands; isAdjustedToUTC=false means naive.
• Python: use zoneinfo from the standard library. If pytz is unavoidable, never pass a pytz zone to the datetime constructor; use zone.localize(), or you get a Local Mean Time offset from the 1800s.

DST transitions

In pandas, tz_localize on a naive index must be told what to do at the two transitions:

idx = df.index.tz_localize(
    "Europe/London",
    ambiguous="raise",        # autumn: the 01:00-02:00 hour occurs twice
    nonexistent="raise",      # spring: the 01:00-02:00 hour does not exist
)

ambiguous="infer" resolves the autumn fold only if the index is strictly increasing and dense through the transition. ambiguous also accepts a boolean array marking which duplicates are DST. nonexistent="shift_forward" is acceptable for scheduling data and wrong for measurements, which cannot have happened at a time that did not exist; those need investigating at the source.

The only way to be immune is to receive UTC or an explicit offset from the source. If the source sends local wall time, you need the source's zone name and its DST rule version, and you should convert at ingest, once, and store the result in UTC alongside the original string.

Event, ingest and processing time

CREATE TABLE readings (
    series_id     text        NOT NULL,
    event_ts      timestamptz NOT NULL,   -- from the source, converted at ingest
    ingested_at   timestamptz NOT NULL DEFAULT now(),
    source_ts_raw text,                   -- the string exactly as received
    value         numeric(18,6),
    PRIMARY KEY (series_id, event_ts, ingested_at)
);

Keeping source_ts_raw costs a few bytes per row and has settled every "was it the feed or was it us" argument you have ever had.

Ordering, late data and watermarks

A watermark is a statement: "I believe all events with event_ts before W have arrived." Windows close when the watermark passes their end; points arriving with event_ts < W are late. The choices are drop, side-output, or reopen the window with a bounded allowed lateness. Stream engines implement this natively; a batch job implements it by choosing which partitions to recompute.

Rules that hold up:

• Derive the watermark from observed lag, not from a constant someone guessed. Track ingested_at - event_ts per source as a distribution and set the watermark from a high quantile of it.
• Late points are never silently dropped. They go to a late-arrivals table with the watermark they missed, and a scheduled job recomputes affected windows with a new revision.
• Store the watermark used by each run in the run's provenance. "Completeness as of watermark W" is a reproducible statement; "we waited a bit" is not.

Clock skew and monotonic clocks

• Sources should be disciplined by NTP at minimum; PTP (IEEE 1588) where sub-millisecond ordering across devices matters. Record which discipline each source claims.
• Measure skew: on every message, compute ingested_at - event_ts. A persistent negative value means the source clock is ahead of yours. A step change means somebody's clock jumped.
• Never compute durations from wall-clock reads inside a process. Use a monotonic clock (time.monotonic() in Python, CLOCK_MONOTONIC on Linux, std::chrono::steady_clock in C++). Wall clocks step backwards on NTP corrections and DST is irrelevant to them only because they are UTC, which people forget.
• For devices with no reliable clock, assign event time at the first trusted hop and record that you did, with the estimated transport delay.

Leap seconds

UTC has had 27 leap seconds inserted since 1972, the most recent at the end of 2016, and TAI is 37 seconds ahead of UTC. Unix time pretends they do not exist, which means a leap second appears either as a repeated second or as a smear, depending on the platform's NTP configuration. The rule is not to model them but to know which behaviour each source has and to expect one-second anomalies on 30 June and 31 December in older data.

Epoch precision

Detect the unit from the magnitude rather than trusting documentation:

Digits (for dates around 2026)	Unit
10	seconds
13	milliseconds
16	microseconds
19	nanoseconds

import pandas as pd

def to_utc(epoch: pd.Series) -> pd.Series:
    digits = epoch.dropna().abs().astype("int64").astype(str).str.len().mode()[0]
    unit = {10: "s", 13: "ms", 16: "us", 19: "ns"}[digits]
    return pd.to_datetime(epoch, unit=unit, utc=True)

Fail loudly if the digit count is not one of the four, or if it changes between files from the same source. In pandas, timestamps are 64-bit integers; at nanosecond resolution the representable range ends in 2262, and pandas 2 lets you pick a coarser unit with .as_unit("ms") when you need dates outside that range.

Session-local times

Markets, plants and networks define their day in a local zone with DST. Convert the session definition to UTC per calendar day using the zone name; do not store a session as a fixed UTC offset. Trading-calendar specifics are covered by the market-data pack; the same discipline applies to shift patterns and maintenance windows.

Validation Procedure

1. Zone-awareness. Every timestamp column is timestamptz or equivalent. Fail the load on naive values.
2. Precision consistency. Digit count of epoch fields is constant within a source. Fractional seconds are consistent with the contract.
3. Plausible range. No event_ts earlier than the series' documented start, none later than ingested_at plus the source's allowed skew.
4. Monotonicity per series by arrival. Track how often event_ts decreases relative to the previous arrival; that is your out-of-order rate, and a step change in it is an incident.
5. DST windows. Count points per local hour across each transition; expect a missing hour in spring and a double hour in autumn only in local-time views, never in UTC.
6. Truncation. Fraction of points at exactly local midnight or at exactly 00 seconds should match the series' cadence, not exceed it.
7. Sentinels. Zero counts at 1970-01-01, 1900-01-01, 1899-12-30 and 9999-12-31.
8. Lag distribution. ingested_at - event_ts quantiles per source, alerted on shift.

-- Out-of-order and skew checks, Postgres
WITH ordered AS (
  SELECT series_id, event_ts, ingested_at,
         LAG(event_ts) OVER (PARTITION BY series_id ORDER BY ingested_at) AS prev_event_ts
  FROM   readings
  WHERE  ingested_at >= now() - interval '1 day'
)
SELECT series_id,
       COUNT(*)                                           AS n,
       SUM((event_ts < prev_event_ts)::int)               AS out_of_order,
       SUM((event_ts > ingested_at + interval '5 s')::int) AS future_events,
       percentile_cont(0.99) WITHIN GROUP (ORDER BY EXTRACT(EPOCH FROM ingested_at - event_ts)) AS lag_p99_s
FROM   ordered
GROUP  BY series_id;

# pandas: DST double-hour and truncation checks
local = df["event_ts"].dt.tz_convert("Europe/London")
per_hour = local.dt.floor("h").value_counts()
suspicious_hours = per_hour[per_hour > 2 * per_hour.median()]

midnight_share = (local.dt.time == pd.Timestamp("00:00").time()).mean()
assert midnight_share < 0.05, f"{midnight_share:.1%} of points at local midnight: date truncation?"

Checklist

• Storage type is an instant with zone; naive timestamps are rejected at ingest.
• Source zone is an IANA name recorded per source; the tz database version in the runtime is pinned and upgraded deliberately.
• event_ts, ingested_at and the raw source string are all kept.
• Epoch unit is validated by magnitude on every file, not assumed.
• ambiguous and nonexistent are explicit in every tz_localize.
• Watermark policy per pipeline is written down and stored with each run.
• Out-of-order rate and lag distribution are monitored per source.
• Sentinel dates and midnight truncation are checked in the load test.

Common Mistakes

• Treating +01:00 as a time zone. It is an offset that is correct for half the year.
• Upgrading the tz database silently in a base image and shifting historical conversions of a zone whose rules changed.
• Using datetime.now() to stamp events instead of taking the source's event time.
• Joining two sources on timestamp equality when they have different precisions; 09:30:00.000 and 09:30:00.000000 are equal, 09:30:00.000 and 09:30:00.0004 are not.
• Resampling in UTC and labelling the buckets with local dates, so the "daily" bar spans two local days after a transition.
• Letting the orchestrator's schedule time stand in for the data's period; a job that runs at 02:00 does not mean the data is for 02:00.

Limits

This skill is about the integrity of the time axis. It does not construct calendars or sessions, which belong to the market-data and scheduling skills, and it does not address value-level anomalies, which the gap and spike skill covers. For purely local applications with a single source and a single zone that never changes, the full discipline is more than you need; even there, store UTC and keep the raw string.