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). 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.

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

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.
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.

// 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
import fs from 'node:fs';
import Papa from 'papaparse';

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'}`);

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.

# Turn any date column into dimensionlabs_timestamp (Unix ms) in the right time zone.
# Usage: python3 fix_timestamps.py times.csv times-fixed.csv
import csv, re, sys
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:

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 reads this form.
Excel date numbers such as 46025.38: see Excel and Excel dates.
Without code: the in-browser converter 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 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.