Standardize Timestamps and Timezones in a Dirty Dataset

1. Profile the column: formats, timezone presence, and impossible values.

import { readFileSync } from 'node:fs';
const rows = readFileSync('crm.csv', 'utf8').trim().split('\n').slice(1);
const col = (r) => r.split(',')[3]; // created_at
const shapes = {};
for (const r of rows) {
  const v = col(r);
  const shape = v.includes('T') ? 'iso' : v.includes('/') ? 'slash' : v.includes('-') ? 'dash' : 'other';
  shapes[shape] = (shapes[shape] ?? 0) + 1;
  if (!/[Zz]|[+\-]\d{2}:?\d{2}$/.test(v)) shapes.zoneless = (shapes.zoneless ?? 0) + 1;
}
console.log(shapes, 'total', rows.length);

Expected output: A shape census prints, for example iso: 61,022, slash: 8,411, dash: 1,930, zoneless: 38,240, quantifying the cleanup scope.

2. Disambiguate day-first versus month-first with an anchor region.

const { parse, format } = require('date-fns');

// anchor: source system was US-based -> month-first
function parseLegacy(v) {
  // 03/04/2026 -> March 4 (US) NOT April 3 (EU)
  const m = v.match(/^(\d{2})\/(\d{2})\/(\d{4})/);
  if (!m) return null;
  const [_, mo, d, y] = m;
  if (+mo > 12) return parse(`${d}/${mo}/${y}`, 'dd/MM/yyyy', new Date()); // auto-detect EU rows
  return parse(`${mo}/${d}/${y}`, 'MM/dd/yyyy', new Date());
}

// detect: which readings are impossible?
for (const v of ['13/04/2026', '03/04/2026', '25/12/2026']) {
  console.log(v, '->', parseLegacy(v)?.toISOString() ?? 'unparseable');
}

Expected output: Ambiguous dates print with their chosen reading, and impossible ones like 13/04 as month-first are auto-corrected to day-first.

3. Attach zone provenance and normalize to UTC.

import { Temporal } from '@js-temporal/polyfill';

// zoneless entries: the CRM server ran in America/New_York
const LEGACY_ZONE = Temporal.Zone.from('America/New_York');

function toInstant(raw) {
  if (/Z$|[+\-]\d{2}:?\d{2}$/.test(raw)) return Temporal.Instant.from(raw); // already absolute
  const legacy = parseLegacy(raw);
  if (!legacy) throw new Error(`unparseable: ${raw}`);
  const zoned = Temporal.PlainDateTime
    .from(legacy.toISOString().slice(0, 19))
    .toZonedDate(LEGACY_ZONE);
  return zoned.toInstant();
}

// output row: normalized + original preserved
const out = { createdAtUtc: toInstant(r.createdAt).toString(), createdAtRaw: r.createdAt };

Expected output: Every row now carries an unambiguous UTC instant plus the original string, so the assumption is visible and reversible.

4. Validate with a round-trip and a known-truth sample.

node -e "const rows=[{raw:'2026-10-22T09:30:00',zone:'America/New_York'}];const {Temporal}=require('@js-temporal/polyfill');rows.forEach(r=>{const i=Temporal.PlainDateTime.from(r.raw).toZonedDate(Temporal.Zone.from(r.zone)).toInstant();const back=Temporal.Instant.from(i).toZonedDateTimeISO(Temporal.Zone.from(r.zone));console.log(r.raw,i.toString(),back.toPlainDateTime().toString())})"

Expected output: The known-truth row shows 09:30 New York becoming 13:30Z and converting back to 09:30, proving the normalization preserves the original wall-clock meaning.

5. Guard future writes at the ingest boundary.

// ingest: refuse zone-less timestamps from new sources
import { z } from 'zod';
const ts = z.string().refine(
  (v) => /Z$|[+\-]\d{2}:?\d{2}$/.test(v),
  { message: 'timestamp must carry an explicit UTC offset' }
);
export const EventSchema = z.object({ at: ts, type: z.string() });
// EventSchema.parse legacy payload -> throws, forcing the fix upstream

Expected output: New data cannot enter without an offset, shrinking the legacy mess instead of topping it up daily.

Problem

Two systems wrote the same column in different zones

Solution

Segment the cleanup by source-system id, applying each zone assumption to its own rows; a single global assumption will silently shift one cohort by hours.

Problem

DST transitions produce midnight-seconds anomalies

Solution

A local time that never existed (spring-forward gap) resolves differently per library; flag these rows for manual review instead of accepting the library default.

Problem

Some timestamps are Unix epoch numbers

Solution

Detect pure digits and treat them as already-absolute seconds or milliseconds by magnitude; document the chosen cutoff, since both scales exist in the wild.