# Fix timestamps > Recipe: convert any date format to Unix milliseconds in the right time zone, fix seconds and wrong years, and keep messages a second apart. ## When you need it `dimensionlabs_timestamp` must be Unix time in milliseconds: a 13-digit number ([conversation format](/data/conversation-format#columns)). Exports rarely give you that. You get some mix of: - clock times with no time zone, such as `2026-01-03 09:12` or `1/3/26 5:45 PM`; - Unix seconds (10 digits), which read as a date in 1970 if you don't multiply by 1,000; - ISO 8601 times with an offset, such as `2026-07-04T08:00:00-06:00`, which are already unambiguous; - day-first dates (`03/01/2026` meaning 3 January) from exports made outside the US; - two-digit years; - several messages with the same time, which then sort in an unknown order; - a known clock error, such as a system that stamped the wrong year. ## Input Example input, `times.csv`. The export's clock times are Denver time. ```csv title="times.csv" sessionId,userId,incoming,text,sent s-1,cus_51,true,Is the drive-through open?,2026-01-03 09:12 s-1,cus_51,false,Yes until 7 PM.,2026-01-03 09:12 s-2,cus_77,true,Order still not ready,1/3/26 5:45 PM s-3,cus_90,true,Where is my refund?,1767457980 s-4,cus_12,true,Loved the new seasonal blend,2026-07-04T08:00:00-06:00 ``` ## Output ```csv title="times-fixed.csv" sessionId,userId,incoming,text,dimensionlabs_timestamp s-1,cus_51,true,Is the drive-through open?,1767456720000 s-1,cus_51,false,Yes until 7 PM.,1767456721000 s-2,cus_77,true,Order still not ready,1767487500000 s-3,cus_90,true,Where is my refund?,1767457980000 s-4,cus_12,true,Loved the new seasonal blend,1783173600000 ``` Read back in Denver time: `s-1` is 9:12:00 and 9:12:01 on 3 January (the reply was moved one second), `s-2` is 5:45 PM, `s-3` is 9:33 AM, and `s-4` is 8:00 AM on 4 July, in daylight saving time. ## Rules - **Every clock time needs a zone.** Find out which zone the source system writes (often the account's zone, sometimes the browser's, sometimes UTC) and name it, for example `America/Denver`. A named zone handles daylight saving time; a fixed offset such as `-07:00` is right for only part of the year. - **Don't let the machine decide.** `new Date('1/3/2026 9:12')` in JavaScript and naive `datetime` values in Python use the time zone of whatever computer runs the script, so the same file converts differently on a laptop and a server. - **Times with a zone or offset are already exact.** Parse them as they are. - **10 digits are seconds, 13 are milliseconds.** Multiply seconds by 1,000. - **Say whether the day or the month comes first.** `03/01/2026` is ambiguous. Look for a day above 12 in the file to be sure. - **Two-digit years:** `26` means 2026 here. If your data goes back before 2000, decide the cut-off yourself. - **Keep messages in a session at least a second apart.** When two messages share a time, keep the file's order and move the later one by 1,000 ms. The conversation format [asks for this spacing](/data/conversation-format#small-rules-that-keep-results-clean). - **Shift only for a known error.** If you know the export clock was off by a year (or an hour), shift every affected row by the same amount, and check a few rows against the source system afterwards. Never "fix" dates by guessing. - **Never fill a missing time with the current time.** A message without a time should be dropped and counted, not dated to the day you ran the script. ## Script Node 18 or later, with `npm install papaparse`. It uses the built-in `Intl` time zone data, so no date library is needed. ```js title="fix-timestamps.mjs" // Turn any date column into dimensionlabs_timestamp (Unix ms), in the right time zone, // with messages in a session at least one second apart. // Usage: node fix-timestamps.mjs times.csv times-fixed.csv const [input, output] = process.argv.slice(2); const COLUMN = 'sent'; const TIME_ZONE = 'America/Denver'; // the zone the export's clock times are in const DAY_FIRST = false; // true for 03/01/2026 meaning 3 January const SHIFT_YEARS = 0; // set to 1 if you know the export stamped the wrong year // Offset (ms) of TIME_ZONE at a UTC instant. function offsetAt(utc) { const p = Object.fromEntries(new Intl.DateTimeFormat('en-US', { timeZone: TIME_ZONE, hourCycle: 'h23', year: 'numeric', month: 'numeric', day: 'numeric', hour: 'numeric', minute: 'numeric', second: 'numeric' }) .formatToParts(new Date(utc)).map((x) => [x.type, x.value])); return Date.UTC(p.year, p.month - 1, p.day, p.hour, p.minute, p.second) - Math.floor(utc / 1000) * 1000; } // Clock time in TIME_ZONE -> Unix ms. Month is 1 to 12 here. function wallToMillis(y, mo, d, h = 0, mi = 0, s = 0) { const guess = Date.UTC(y, mo - 1, d, h, mi, s); return guess - offsetAt(guess - offsetAt(guess)); } function toMillis(v) { v = String(v).trim(); if (/^\d{13}$/.test(v)) return Number(v); // already milliseconds if (/^\d{10}$/.test(v)) return Number(v) * 1000; // seconds if (/(Z|[+-]\d{2}:?\d{2})$/.test(v)) return Date.parse(v); // ISO 8601 with a zone let m = v.match(/^(\d{4})-(\d{2})-(\d{2})[ T](\d{1,2}):(\d{2})(?::(\d{2}))?$/); if (m) return wallToMillis(+m[1], +m[2], +m[3], +m[4], +m[5], +(m[6] || 0)); m = v.match(/^(\d{1,2})\/(\d{1,2})\/(\d{2}|\d{4}) (\d{1,2}):(\d{2})(?::(\d{2}))?\s*([AP]M)?$/i); if (m) { const [a, b] = [+m[1], +m[2]]; const [mo, d] = DAY_FIRST ? [b, a] : [a, b]; const y = m[3].length === 2 ? 2000 + +m[3] : +m[3]; // 26 -> 2026 let h = +m[4] % 12; if (!m[7]) h = +m[4]; else if (m[7].toUpperCase() === 'PM') h += 12; return wallToMillis(y, mo, d, h, +m[5], +(m[6] || 0)); } return NaN; } function shiftYears(ms, years) { if (!years) return ms; const d = new Date(ms); d.setUTCFullYear(d.getUTCFullYear() + years); // 29 February rolls to 1 March return d.getTime(); } const { data } = Papa.parse(fs.readFileSync(input, 'utf8'), { header: true, skipEmptyLines: 'greedy' }); const bad = []; const rows = data.map((r, i) => { const ms = shiftYears(toMillis(r[COLUMN]), SHIFT_YEARS); if (Number.isNaN(ms)) bad.push(i + 2); // file line number const { [COLUMN]: _, ...rest } = r; return { ...rest, dimensionlabs_timestamp: ms }; }).filter((r) => !Number.isNaN(r.dimensionlabs_timestamp)); // Same-second messages: keep file order, push each one a second after the one before. const last = new Map(); for (const r of rows) { const prev = last.get(r.sessionId); if (prev !== undefined && r.dimensionlabs_timestamp < prev + 1000) r.dimensionlabs_timestamp = prev + 1000; last.set(r.sessionId, r.dimensionlabs_timestamp); } fs.writeFileSync(output, Papa.unparse(rows, { newline: '\n' }) + '\n'); console.log(`${rows.length} rows written; unreadable dates on lines: ${bad.join(', ') || 'none'}`); ``` ```bash node fix-timestamps.mjs times.csv times-fixed.csv # 5 rows written; unreadable dates on lines: none ``` ## In Python Python 3.9 or later: `zoneinfo` is in the standard library. It writes the same file. ```python title="fix_timestamps.py" # Turn any date column into dimensionlabs_timestamp (Unix ms) in the right time zone. # Usage: python3 fix_timestamps.py times.csv times-fixed.csv from datetime import datetime from zoneinfo import ZoneInfo COLUMN, TZ, DAY_FIRST = "sent", ZoneInfo("America/Denver"), False def to_millis(v): v = v.strip() if re.fullmatch(r"\d{13}", v): return int(v) if re.fullmatch(r"\d{10}", v): return int(v) * 1000 if re.search(r"(Z|[+-]\d{2}:?\d{2})$", v): return int(datetime.fromisoformat(v.replace("Z", "+00:00")).timestamp() * 1000) for fmt in ("%Y-%m-%d %H:%M", "%Y-%m-%d %H:%M:%S", "%Y-%m-%dT%H:%M:%S", "%d/%m/%y %I:%M %p" if DAY_FIRST else "%m/%d/%y %I:%M %p", "%d/%m/%Y %H:%M" if DAY_FIRST else "%m/%d/%Y %H:%M"): try: return int(datetime.strptime(v, fmt).replace(tzinfo=TZ).timestamp() * 1000) except ValueError: pass return None src, dst = sys.argv[1], sys.argv[2] with open(src, newline="", encoding="utf-8") as f: rows = list(csv.DictReader(f)) out, last, bad = [], {}, [] for i, r in enumerate(rows, start=2): ms = to_millis(r.pop(COLUMN)) if ms is None: bad.append(i) continue prev = last.get(r["sessionId"]) if prev is not None and ms < prev + 1000: ms = prev + 1000 last[r["sessionId"]] = ms out.append({**r, "dimensionlabs_timestamp": ms}) with open(dst, "w", newline="", encoding="utf-8") as f: w = csv.DictWriter(f, fieldnames=list(out[0].keys()), lineterminator="\n") w.writeheader() w.writerows(out) print(f"{len(out)} rows written; unreadable dates on lines: {bad or 'none'}") ``` ## Check the result Convert a few output values back and compare them with the source system's screen: ```bash node -e "console.log(new Date(1767487500000).toLocaleString('en-US', { timeZone: 'America/Denver' }))" # 1/3/2026, 5:45:00 PM ``` ## Variations - **Month names**, such as `Oct 3 2025 9:12AM` (some exports pad the day with two spaces): collapse repeated spaces first, then parse. The [toolkit](/transform/toolkit) reads this form. - **Excel date numbers** such as `46025.38`: see [Excel and Excel dates](/transform/recipe-excel). - **Without code**: the in-browser [converter](/data/convert-a-file) reads Unix seconds and milliseconds, Excel date cells, ISO 8601, and dates such as `1/3/2026 9:12 AM`, in a time zone you pick from its list. It doesn't read month names or shift years. - **The [toolkit](/transform/toolkit)** does all of this with `"timestamp": { "timeZone": "America/Denver", "dayFirst": false }` and spaces messages with `"spaceMessagesMs": 1000`. It doesn't shift years; use this script for that.