Normalize timestamps without losing their original timezone — what should the operator record?
Two evidence tables can describe the same instant with different offsets, or different instants with the same displayed clock time. Normalize carefully before joining. The operational aim is a traceable comparison value, not a cosmetic uniform date column that hides ambiguous or missing source information.
Keep raw time strings, offsets and precision alongside UTC comparison values.
Use raw_timestamp, parsed_instant, source_offset and precision fields in the working projection, retaining the original response reference for each row. An original offset-bearing string supports reconstruction of the conversion, while a separately formatted UTC display value alone can hide a mistaken parsing assumption.
Record parser_revision and unresolved_time_count from actual validation; never describe inferred time precision as directly returned evidence. A useful validation artifact lists raw value, parsed instant and parse state for permitted sample rows, plus aggregate exception counts drawn from the actual tested input.Declare timestamp columns
Use raw_timestamp, parsed_instant, source_offset and precision fields in the working projection, retaining the original response reference for each row. An original offset-bearing string supports reconstruction of the conversion, while a separately formatted UTC display value alone can hide a mistaken parsing assumption.
Choose explicit parsing rules
Accept known offset-bearing timestamps and route offset-free values to review unless the source's documented timezone is established. Do not use the machine's current timezone for strings lacking offsets; the same input would otherwise normalize differently when a colleague reruns the transformation elsewhere.
Test equivalent instants
Use labeled fixture values 2026-11-01T08:00:00Z and 2026-11-01T09:00:00+01:00 to confirm they normalize identically. Also include an offset-free fixture and expect unresolved-zone, demonstrating that the parser does not silently treat every valid-looking clock string as an exact instant.
Inspect conversion failures
Keep invalid strings and unknown-zone records outside instant joins, with a reason rather than a guessed local timezone. Review daylight-saving ambiguities and date-only values independently; both can parse into software date objects while still lacking the precision needed for this table's join.
Release the normalized table
Record parser_revision and unresolved_time_count from actual validation; never describe inferred time precision as directly returned evidence. A useful validation artifact lists raw value, parsed instant and parse state for permitted sample rows, plus aggregate exception counts drawn from the actual tested input.
Keep the boundaries clear.
- Keep raw time strings, offsets and precision alongside UTC comparison values.
- Operational record: Declare timestamp columns. Context badges do not certify access.
Start with a read-only check: ask your agent to report actual source coverage before collecting records or exporting an audience.
Primary references
Documentation reviewed 2026-11-15. Product capabilities, client availability and plans can change. Third-party marks identify their products, not an affiliation.