A mobile client sends event times as "2027-03-28T02:30:00" with no offset. Nightly reporting has always been slightly off for European users, and on one Sunday in March about 40 minutes of events vanish from the daily rollup while a different Sunday in October shows some events twice. Diagnose it.

The first three things to look at

1. The wire format of the timestamp, not the database column. 2027-03-28T02:30:00 has no offset and no zone, so it is a wall-clock reading, not an instant. Something downstream has to guess which instant it meant, and the guess is where the drift lives.
2. What the ingest service does with it. Almost always it parses the string in the server's own zone or in UTC, which silently relabels a Berlin 02:30 as a UTC 02:30. That is a fixed one or two hour error for every European event, which matches "slightly off, always".
3. The dates of the two anomalies. The last Sunday in March and the last Sunday in October are the European daylight-saving transitions, and they explain the two different symptoms.

The diagnosis

A wall-clock time without an offset does not identify a point in time, and on two days a year it is either impossible or ambiguous.

At the spring transition, local clocks jump from 02:00 to 03:00. The hour 02:00–02:59 does not exist, so every timestamp inside it is unparseable or, worse, silently shifted by a lenient parser. That is the 40 missing minutes. At the autumn transition, 02:00–02:59 happens twice, and the same wall-clock string maps to two different instants an hour apart. Order breaks, deduplication by (user, timestamp) collapses two real events into one, and a rollup bucketed by local hour counts some events in two buckets.

The misleading clue is the database. Columns are usually timestamptz and look correct, because the damage happened at the parser, before storage. Querying the stored data will not reveal the bug; only comparing the stored instant against the device's own recollection will.

The fix

Store three values, and name them so nobody collapses them again:

• The instant, as UTC, derived on the client where the zone is actually known. 2027-03-28T02:30:00+01:00 is self-describing and unambiguous; prefer RFC 3339 with an explicit offset on the wire.
• The original UTC offset, because +01:00 is information you cannot recover from the UTC instant and need for any "what time was it for the user" question.
• The IANA zone id (Europe/Berlin), because the offset alone cannot answer "the same time next week" when a transition falls in between. Zone ids change too, so store the id and resolve rules at read time rather than baking offsets into future schedules.

Then make the contract enforce it: reject a timestamp without an offset at the API boundary with a 400, rather than accepting it and guessing. A tolerant reader here is a corrupting reader.

When this is the wrong answer

For a date that is genuinely a calendar date rather than an instant (a birthday, an invoice period, a hotel check-in day), UTC conversion is the bug, not the fix: store the local date as a date and keep it out of the instant pipeline entirely. For a server-generated event, the client's clock is not worth trusting at all and the server's instant is the record. And for a system with one deployment region and users in one zone, the error is constant and invisible for years, which is exactly why it ships: the planning rule is that the cost of fixing a timestamp contract rises with every consumer that has already stored the wrong thing.