How do you store UTC and render local time in a multi-site lab data loader?

## Why one canonical instant A loader that ingests results from sites in several regions has to answer questions that span sites: order all readings by when they were taken, find everything in a window, compute a turnaround duration. Those questions are only well-defined on a single time line. Local wall clocks are not one — two readings labelled 09:00 at different sites are not simultaneous, and within one site a wall clock is not even monotonic across a rule transition. So the discipline is boring and absolute: **convert at the boundary, store UTC, render local.** The middle of the system never sees a local wall time. ## Ingest Inbound values arrive in three shapes and each needs a different move: * **Already aware** (an ISO string with an offset, say) — `datetime.fromisoformat` gives you an aware value; `astimezone(datetime.timezone.utc)` normalizes it. * **Naive, with a known site zone** — attach the zone with `replace(tzinfo=ZoneInfo(site_key))`, *then* convert. Attaching is a statement of fact; converting a naive value directly would let the machine's own configuration decide what it meant. * **Naive, with no known zone** — this is missing data, not a formatting problem. Reject it at the boundary rather than guessing, because a guess is unrecoverable once it is stored. Do this once, in the loader's parsing layer, and give the rest of the code a type that is always aware and always UTC. ## Storage Two columns: the instant, and the site's IANA key. The key belongs to the site record rather than to each reading, unless a site can move. What not to store: * the **abbreviation** (`CST`, `IST`) — ambiguous worldwide and season-dependent; * a **numeric offset alone** — fine as a record of what a sender claimed, useless for re-rendering a neighbouring date, and unable to describe the region; * a **local wall time with no zone** — the classic irreversible loss. For a past event the UTC instant is the truth and the key is presentation metadata. For a *scheduled future* civil time — "the site's 08:00 collection round" — the local time plus the key is the truth, because the rules may change before it arrives; resolving it to UTC early bakes in today's rules. ## The shared-state trap in a threaded loader The tempting shortcut when rendering per site is to set the process time zone: assign `os.environ["TZ"]`, call `time.tzset()`, format, move on. In a worker pool this is a genuine race on shared mutable state. `os.environ` and the C library's zone state are process-wide, so two workers handling different sites interleave and each formats some rows under the other's zone. The corruption is silent, plausible-looking and impossible to reconstruct afterwards, because the output is a valid timestamp — just the wrong one. The same reasoning rules out bare `astimezone()` with no argument in worker code: it reads the same ambient configuration, so the answer depends on the host rather than on the row. Pass an explicit `ZoneInfo` object derived from the row's site. `ZoneInfo` is immutable and cached per key, so all workers share one instance safely and constructing it per row costs almost nothing. ## Memory and throughput When a batch's working set reaches a couple of gigabytes — 2.4 GB is enough to make allocation behaviour visible — the temptation is to blame the datetime objects. Usually they are not the problem: because zones are shared instances, an aware datetime is not meaningfully heavier than a naive one, and it is the surrounding row objects that dominate. The real wins are the ordinary ones: stream in batches rather than materializing the whole load, keep timestamps as UTC instants (or integers) through the numeric stages, and render strings only for the rows a human will actually see. Formatting every row to local text "just in case" is what turns a large batch into a memory problem. ## Reproducibility One more consequence worth stating in an interview: because rendering depends on the tz database revision the process can read, a report regenerated a year later can legitimately differ from the original for dates near a rule change. If reproducing a rendered report byte-for-byte matters, pin the data source deliberately — depend on the packaged database rather than whatever the base image carries — and record which revision produced a published artefact. The stored UTC instants never change; only the local rendering can.