Time Zones, DST and Date Formats: Datetime Hygiene for Analysts

The campaign launched at midnight — in which time zone? The Berlin office logged it in CET, the server in UTC, and the analyst's laptop in Eastern. Every join, cohort, and daily aggregate downstream now blends three different midnights into one. Datetime bugs are the cockroaches of data work: invisible in samples, everywhere in production, and immune to every fix except discipline.

Store UTC, Display Local, Never Mix

One rule prevents most damage: store and compute in UTC; convert to local time only for display. Timestamps with explicit offsets (2026-03-29T01:30:00+01:00) are unambiguous; bare "local" datetimes are not a timestamp at all — they are a wall-clock reading that may have happened twice (fall-back) or never (spring-forward). If your source gives you naive local times, attach the zone at ingest, immediately, before the knowledge of which zone it was evaporates.

Daylight saving purely punishes aggregators. On spring-forward Sunday the day has 23 hours; on fall-back, 25, with one hour repeated. Hourly charts show a gap and a double-spike that look like outages and surges. Annotate DST transitions on every hourly dashboard, or aggregate ambiguous days with explicit handling — never let the chart silently imply a traffic collapse that was actually a clock change. The same transition weeks poison seasonality estimates if you feed them raw hourly data.

Parsing: The 01/02/03 Problem

Is 01/02/03 January 2 or February 1, 2003 or 2001? Every component is ambiguous, and real datasets mix conventions within a single column — US exports beside European manual entries. Defenses, in order of strength:

• Constrain at the source. Date pickers and ISO-8601 (YYYY-MM-DD) exports eliminate the problem. Every free-text date field is a bug report from the future.
• Detect, don't assume. Values above 12 in the first position reveal day-first data; values above 12 in the second reveal month-first. Mixed columns need row-level rules or source-level splitting.
• Quarantine the ambiguous. Dates like 01/02/03 that are valid under both conventions must be flagged, not guessed. A guessed date is a wrong date with confidence.

Excel adds its own trap: it silently converts gene names and part numbers to dates ("SEPT2" becomes September 2) and stores dates as day-counts from an epoch that differs between Windows and old Mac files. Any date column that passed through a spreadsheet deserves the full CSV-cleaning treatment before you trust a single value.

A Display Checklist

When datetimes reach humans: always show the zone abbreviation next to times ("14:30 CEST", never bare "14:30"); prefer unambiguous formats ("29 Mar 2026") over slashed ones in every locale; and keep one canonical "business day" definition (UTC day? store-local day?) documented where analysts can find it — half of all "the numbers don't match" disputes are two teams aggregating different midnights. Datetime hygiene is unglamorous, but it is load-bearing: every cohort, forecast, and funnel upstream of it inherits whatever sloppiness you allow here.