Skip to content

Normalize timestamps without losing their original timezone

Keep raw time strings, offsets and precision alongside UTC comparison values.

Guides

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.

Practical operations check
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.
01 / Guides

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.

02 / Guides

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.

03 / Guides

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.

04 / Guides

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.

05 / Guides

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.

Know what social media shows about you.
Start with a question.

Get started
montage