Extracting Timestamps From a Chat Transcript With Inconsistent Formats

You have a chat export where the first forty messages are stamped 2026-07-12T09:41:07Z, the middle section says “Yesterday 3:14 PM”, and the last six say “2m ago”. Sorting produces nonsense and every parser you try returns nulls for two of the three. Here is why the mixture exists and how to resolve it without inventing a single instant.

The symptom

The mixture is not corruption. It is a rendering artifact: the export was produced by copying what the interface displayed, and interfaces display time relative to when you are looking at them. Recent messages get “2m ago”, today’s get a bare clock time, yesterday’s get a word, older ones get a date, and anything the exporter pulled from an API kept its machine format. A single file can therefore contain four or five conventions, each correct in its own frame of reference.

That framing tells you what the fix is. Each convention is missing a different piece of information, and the job is to identify which piece and to find it — not to write a bigger regex. Only one of those missing pieces is genuinely unrecoverable, and knowing which one is most of the value here.

Relative times need a capture time

“2m ago” means two minutes before the moment the page was rendered. It does not mean two minutes before you run your parser. If the export was taken three weeks ago and you resolve against the current clock, every relative timestamp in the file lands three weeks late, in an order that is internally consistent and therefore looks fine.

So find the capture time. In descending order of reliability: an explicit “exported at” line in the file, which many tools write; the HTTP Date or a header from the response if you captured it yourself; the filename, which frequently embeds a date; the file’s modification time, which survives some copies and not others; and finally the newest absolute timestamp in the file, which is a lower bound and nothing more.

If you cannot establish a capture time at all, say so in the data. Relative timestamps in that file are orderable but not datable: you know “5m ago” came before “2m ago”, and you know nothing else. Record the ordering, leave the instant null, and set a resolution code that says why. A null with a reason is recoverable when somebody later remembers when the export was taken; a fabricated instant is not, because nothing about it looks wrong.

03/04/2026 and the file-level test

Where absolute dates appear in numeric form, day-month-year and month-day-year are indistinguishable for any day of 12 or less. This is roughly the first 12 days of every month, so a mis-read affects a large minority of your data and silently reorders it.

There is a good test, and it works at the level of the file rather than the individual date. Scan every numeric date in the export. If any first component exceeds 12, the format is day-first for the whole file. If any second component exceeds 12, it is month-first. Exports are generated by one program in one locale, so the convention is uniform within a file even though it varies between files, and one unambiguous date anywhere settles every ambiguous one.

function detectDateOrder(dates) {           // dates: [[a, b, y], ...]
  let dayFirst = false, monthFirst = false;
  for (const [a, b] of dates) {
    if (a > 12) dayFirst = true;
    if (b > 12) monthFirst = true;
  }
  if (dayFirst && monthFirst) return "conflict";   // two sources merged
  if (dayFirst)   return "DMY";
  if (monthFirst) return "MDY";
  return "undetermined";                            // do not guess
}

Three outcomes rather than two is the point. undetermined means every date in the file happened to fall in the first twelve days, and the honest response is to flag the file for a human or to take the order from an out-of-band fact such as the tenant’s locale — not to default to your own. conflict means the file contains dates from two different renderers, which usually means somebody concatenated exports, and it is worth catching because every other assumption you are about to make is also file-wide.

Two smaller ambiguities live here too. A two-digit year needs a century rule and the sensible one is a sliding window with the pivot recorded. And a clock time with no meridiem marker in a locale that uses one is unresolvable between morning and afternoon — the monotonicity check below catches many of these, because a conversation that jumps backwards twelve hours is usually a lost “PM”.

Whose clock, and which offset

A bare wall-clock time carries no zone, and the zone it implies is almost never the one you want. A widget transcript renders in the agent’s browser zone. A phone export renders in the phone’s zone. An API dump is usually UTC. When a support conversation crosses two continents, the customer’s “3:14 PM” and the agent’s are the same instant displayed twice, and if you resolve them independently you will produce a conversation in which the answer precedes the question by eight hours.

Store an IANA zone identifier rather than a fixed offset wherever you can, because an offset is only correct for an instant and the zone is correct for the region. This matters concretely at the daylight-saving boundaries: in a zone that moves its clocks back, one wall-clock hour occurs twice in a year and a local time inside it maps to two instants; in the spring transition, an hour does not exist at all and a local time inside it maps to none. A transcript timestamped in local time during either window is genuinely ambiguous or genuinely impossible, and the only correct handling is to pick a documented rule — earlier of the two, say — and mark the record.

Where the source gives you an epoch number, check its magnitude before trusting it: a ten-digit value is seconds and a thirteen-digit value is milliseconds, and reading milliseconds as seconds puts the message tens of thousands of years in the future. Some workspace formats use a decimal string whose fractional part is a per-channel disambiguator rather than sub-second precision, so it is safe to sort on but not to present as a time.

The resolution procedure

1. Establish the capture instant and the file’s date order first, as file-level facts, before parsing a single row. Both are inputs to every row and neither can be decided from one row.
2. Strip Unicode format characters from each timestamp string. Invisible directional marks around bracketed timestamps will defeat an anchored pattern and produce a null that looks like a missing value.
3. Classify each string into a shape — relative, day-word plus clock, clock only, numeric date, ISO instant, epoch number — with an explicit pattern per shape and an unrecognised bucket. Count the buckets; an unexpectedly large bucket is a shape you have not handled, and it is much cheaper to find here than downstream.
4. Resolve each shape against its anchor: relative and day-words against the capture instant, clock-only against the date of the nearest preceding dated message, numeric dates against the file order, ISO instants against nothing because they are already complete.
5. Emit the original string, the resolved instant, the zone and its source, and a resolution code, for every row. Never overwrite the original text.

{
  "index": 41,
  "ts_text": "Yesterday 3:14 PM",
  "ts_shape": "day_word_clock",
  "ts_instant": "2026-07-13T14:14:00Z",
  "ts_zone": "Europe/London",
  "ts_zone_source": "export_header",
  "ts_precision": "minute",
  "ts_resolution": "relative_to_capture",
  "ts_ambiguous": false
}

ts_precision earns its place. A message resolved from “2m ago” is accurate to a minute at best; one from an ISO instant is accurate to a second or better. Storing them both as timestamps with equal apparent precision invites somebody to compute a response-time distribution across the mixture and get a number with no meaning.

The check that finds what you got wrong

After resolution, the timestamps in turn order must be non-decreasing. Messages in a transcript are already in the order they happened, so any backwards step is your parse being wrong, and the position of the step tells you which row and usually which shape.

• A jump backwards of almost exactly twelve hours is a lost meridiem marker.
• A jump backwards of days, on a single row, in a file with numeric dates, is that row using the other date order — which means your file-level detection found a conflict you ignored.
• A jump backwards of hours at a fixed boundary is two zones being resolved as one.
• Everything after a certain row being weeks late is the relative block resolving against the wrong anchor, which is the capture-time failure and the most common of all.

Run it as an assertion over every file, not as a spot check, and report the count of violations per file rather than aborting — one bad row should not cost you the export. Correct, comparable timestamps are the precondition for everything else you might want from a transcript, including turn segmentation and speaker identity, since consecutive-turn merging depends on a gap you can measure.