A column of local wall-clock readings crosses a clock shift. Which two local times cause trouble when a zone is attached, and why?

## What a shift does to the local clock A zone that shifts its clocks seasonally does it by changing the displacement from UTC at a fixed moment. The world timeline is untouched — no moment is created or destroyed — but the mapping from moments to clock readings stops being one-to-one for an hour in each direction: - **When clocks go forward**, an hour of local readings is skipped. If the clock jumps from 02:00 to 03:00, then 02:30 **never occurred** in that zone that day, and a row holding it names no moment at all. - **When clocks go back**, an hour of local readings occurs twice. If the clock returns from 03:00 to 02:00, then 02:30 occurred **twice**, an hour apart, and a row holding it names two moments with nothing in the value to choose between them. Everything else in this subject follows from those two sentences. ## Why it reaches your column at all It reaches you whenever the capture side recorded a clock reading rather than a moment: a device writing its local time, an export from a system whose display zone is the office's, a form where a human typed a time, a log line formatted locally with no displacement written out. It does not reach you if the capture side recorded the moment, which is the whole argument for doing so. ## What attaching a zone does with the two cases Designs differ here more than anywhere else in this subject, and the difference is invisible unless you go looking: | Case | What the local reading names | Behaviours seen across designs | |---|---|---| | The repeated hour | Two moments, one each side of the shift | Raise as ambiguous; silently take the earlier; silently take the later; take the side supplied by a separate flag | | The skipped hour | No moment at all | Raise as nonexistent; move it forward past the shift; move it back; produce the absent-time marker | A design that raises hands you a decision to make. A design that resolves silently hands you a column that looks complete and is wrong for at most an hour of rows a year — which sounds negligible until that hour is a settlement window, a shift handover or an overnight batch. ## The symptoms in data 1. **Duplicate keys inside one hour.** If the local stamp is part of a natural key, the repeated hour produces colliding rows once a year, and a de-duplication step then drops real records. 2. **An ordering that is not monotonic.** Sorting on the local column places the second pass through the repeated hour among the first pass's rows. Any operation whose precondition is monotonic time order is then running on an input that violates it, and in several designs nothing verifies that precondition. 3. **An hour counted twice, or an hour missing.** A total per local hour shows one hour with roughly double the traffic, or one hour with none, and the year-on-year comparison reads like an incident. 4. **A hole in a derived sequence.** A pipeline expecting one position per local hour finds the skipped hour absent, and either records a hole or shifts everything after it by one position. ## What to do about it - **Capture moments.** The only durable fix is upstream: record the moment, or record the clock reading together with the displacement in force. Either is unambiguous; a bare local reading is not. - **Carry a disambiguating marker when you cannot.** Where a feed must stay in local wall-clock, a per-row marker for which pass through the repeated hour a record belongs to is the minimum that makes the column recoverable. - **Make the attachment explicit.** When you attach a zone to a local column, decide in the code what happens to ambiguous and skipped readings rather than accepting whatever the default is, and write the decision where the next reader will find it. - **Verify after attaching, not before.** Check that the resulting moments come out monotonic where you expect them to, and check the row count against what the span should contain. Both cost one pass and catch a silent resolution. - **Do not sort the local column to establish time order.** Sort on the moment. The local column is a rendering. ## What an interviewer is listening for That you can name both pathologies unprompted, in both directions, and that you say what varies rather than asserting one behaviour as the rule. The weak answer is that the tool will tell you; it may, and it may not. The strong answer moves the problem upstream to the moment of capture and, where that is impossible, makes the resolution an explicit reviewed decision with a check standing behind it.