Skip to content

Systems and records

Event log clock skew correction: rebuilding order when system clocks disagree

By SourceX Editorial · Updated

9 sections

Short answer

Do not sort a merged log by raw timestamps. Normalize each source to UTC while keeping the raw value, then use event pairs whose order is certain, such as a call that must start before the case citing it, to estimate each system's clock offset per period. Apply the smallest correction that removes violations, leave unlinked ties as concurrent, and publish an event log clock skew correction report with confidence labels.

Record timeline

Event log clock skew

This page covers event log clock skew correction: rebuilding order when system clocks disagree.Illustrative

Key takeaways

  • Converting to UTC fixes offsets, not disagreeing clocks; measure clock differences from anchor pairs whose order is certain.
  • Each anchor pair is an inequality, and together they bound the correction for a source; a one-sided bound must be labelled.
  • Estimate per day or week, because drift and clock steps make a single constant correction wrong.
  • A violation rate that stays high after correction usually means bad joins, not bad clocks.
  • Keep the raw value, interpretation rule, corrected UTC value and correction ID for every event.

How do you reconstruct event order when source system clocks disagree?

Do not sort the merged log by raw timestamps and trust the result. First normalize every source to UTC while keeping its original value and offset. Then find pairs of events whose order is certain for business reasons, such as a call that must end before the ticket logging it is created, and use them to measure how far each system's clock sits from the others. Apply a correction per source and per period, order the remaining ties by causal links and sequence numbers, and report how confident each reconstructed case is.

Steps

How do you reconstruct event order when source system clocks disagree

The principle comes from distributed systems. Lamport's 1978 paper shows that in a system of separate processes it is sometimes impossible to say which of two events happened first, and that the happened-before relation is therefore only a partial ordering. A merged business event log is such a system. Some orderings are known from cause and effect; the rest are estimates, and the dataset should say which is which.

The procedure, step by step:

  • Inventory each source: column type, whether an offset is stored, precision, and whether the value is occurrence time or entry time.
  • Normalize to UTC, keeping the raw value, the original offset and the rule used to interpret it.
  • Collect anchor pairs across each pair of systems where the order is certain.
  • Estimate a correction interval per source and period from the anchor pairs, and check for steps or drift.
  • Apply the smallest correction that removes violations, and leave untouched any source with no anchors.
  • Order ties using sequence numbers, parent and child links and lifecycle transitions; mark residual ties as concurrent.
  • Reconcile event counts back to each source and publish the correction report with the dataset.

Why do timestamps from different business systems disagree?

Clock skew in business data usually has a mundane cause, and the cause decides the correction. The table lists the common ones with the symptom each leaves in a merged log.

Two documented behaviors are worth knowing. Microsoft explains that the Windows Time service normally adjusts the clock rate to converge on the correct time, but sets the clock directly when the skew is too large to correct that way, which can produce a jump. PostgreSQL documents that a timestamp without time zone silently ignores any time zone indication in the input, so local times can enter a database with their offset lost.

Why do timestamps from different business systems disagree?
CauseSymptom in the merged logHow to detect itCorrection
Local time stored without an offsetWhole sources shifted by whole hoursOffsets between systems cluster at hour multiplesReinterpret with the source's documented zone; keep the rule used
Daylight saving transitionsDuplicated or missing local hour twice a yearViolations concentrate on transition datesResolve the repeated hour with sequence data; flag what stays ambiguous
Clock stepped by time syncSudden jump, sometimes backward, within one sourceChange point in the offset series for that sourcePiecewise correction split at the step
Slow drift between syncsOffset that grows steadily over days or weeksTrend in the per-day offset estimatePer-period correction, never one constant
Entry time recorded instead of occurrence timeEvents appear minutes or days after their effectsLarge positive gaps with no fixed patternDo not shift; label as entry time and lower confidence
Coarse precisionMany events share the same second, minute or dateHigh share of exact ties within a caseOrder ties by sequence or links, not by the clock

What should the clock skew detection and correction report include?

The report travels with the dataset so that anyone can see which orderings are observed, which are corrected and which are guesses. Each section below has a method and a pass condition a reviewer can check. This is a template for the report; it contains no results.

What should the clock skew detection and correction report include?
Report sectionContentsMethodPass condition
Source inventorySystem, table, column type, stored offset, precision, occurrence or entry timeSchema review and documentationEvery timestamp column classified
Normalization rulesZone assumed per source, daylight saving handling, raw value retainedDocumented rule per sourceRaw value and rule kept beside the UTC value
Anchor pairsPair types with the business reason the order is certain, count per system pair and periodJoin on shared identifiersEnough pairs per period to estimate an interval
Offset estimatesCorrection interval per source and period, change pointsBounds from anchor pairs, change point reviewInterval width stated; steps explained
Violations before and afterShare of anchor pairs out of order before and after correctionRecount on held-out anchor pairsViolations fall on pairs not used to estimate
Corrections appliedConstant, piecewise or none, with the value per source and periodVersioned correction tableEvery corrected event points to one correction ID
Residual ambiguityTies and orders that remain uncertainCount per caseAmbiguous orderings labelled concurrent, not forced
ReconciliationEvent and case counts per source before and after mergeTotals compared to source extractsNo events lost or duplicated
Case confidenceHigh, medium or low per reconstructed case with reasonRule-based from the sections aboveEvery case carries a confidence label

How do you estimate the offset between two systems from anchor pairs?

Treat each anchor pair as an inequality. Suppose c is the amount to add to system B's timestamps. If an event in A must precede an event in B, the corrected times must satisfy tA < tB + c, so c must be greater than tA minus tB. If an event in B must precede one in A, then c must be less than tA minus tB. The lower bound is the largest value from the first kind of pair and the upper bound is the smallest value from the second kind.

When both kinds of pair exist, the true correction lies in that interval, and a narrow interval is strong evidence. When only one direction exists, you get a bound and nothing more: choose the smallest correction that removes the violations, and say in the report that the estimate is one-sided. Estimate per day or per week rather than once for the whole period, because drift and steps make a single constant wrong at both ends.

Network time protocols face the same problem from the other side. Microsoft's guidance on accurate time notes that NTP calculations assume the network path is symmetrical, and that asymmetrical paths reduce accuracy. Even well-synchronized servers can therefore disagree by more than the gaps between fast-moving business events, which is why anchor pairs, not trust in synchronization, should drive the correction.

Business systems usually offer several kinds of anchor pair, each matched on a shared identifier:

  • A phone call starts before the CRM case that stores its call ID is created.
  • An email is sent by one system before the ticketing system logs its arrival, matched on the Message-ID header.
  • A purchase order is approved in the ERP before the supplier portal shows it as received.
  • A case is created before any credit memo, refund or work order that references the case number.
  • A user logs in to an application before that session performs an action, matched on session ID.

How can drift, a bad join or a missing start time mislead the reconstruction?

Yes, and each one fails differently. Drift makes a correction that is right in week one wrong in week six; it shows up as a trend in the per-period interval, and the fix is shorter periods. An identifier mismatch produces false anchor pairs, for example when a ticket's call ID points to a transferred call leg rather than the original call; those pairs create violations that no clock correction can remove, so a residual violation rate that stays high after correction usually signals bad joins rather than bad clocks.

An unrecorded transition is the hardest. If a system records only the completion of a step, its timestamp says nothing about when the step started, and two steps can overlap even though the log shows them in sequence. The XES event log standard separates the time:timestamp attribute from lifecycle:transition values such as start and complete for this reason. Where only completions exist, the reconstructed order of overlapping steps should be labelled as completion order, not as the order the work happened.

Worked example: a CRM, a phone system and a billing system disagree (illustrative)

This example is invented to show the method; every duration and count is illustrative. A support team merges three sources into one event log: call records from a cloud phone system, cases from a CRM, and credit memos from a billing system. In the raw merge, 31 percent of cases show the CRM case created before the call that prompted it had started.

Anchor pairs come from two rules: a call must start before the case that references its call ID is created, and a case must be created before a credit memo that references the case. From one week of pairs, the CRM-to-phone interval for the correction is between 70 and 95 seconds; the billing system needs no correction. Applying an 80-second correction to the phone timestamps, re-estimated weekly, cuts violations on held-out pairs to under 1 percent.

The residue is informative. The remaining violations all involve calls transferred between agents, where the referenced call ID belongs to the second leg. Those cases are labelled medium confidence and the join is corrected, not the clock. The report records the weekly correction table, the held-out violation rates and the transfer rule, so a buyer can reproduce the order exactly.

When should you leave the timestamps alone?

When the events have no causal link, when a source records entry time rather than occurrence time, or when there are too few anchor pairs to support an estimate. In those cases a correction adds confidence the data does not have. Two events with no causal connection may be concurrent, and Lamport's analysis is precise on this point: the partial order is all the evidence supports.

Daylight saving is the other case for restraint. Python's datetime documentation describes the repeated interval when clocks roll back, where one wall time maps to two moments, and provides a fold attribute to tell them apart. If a source stored local time without that distinction, events in the repeated hour are ambiguous unless sequence numbers or links resolve them; label them rather than guessing.

Which timestamp fields keep the correction reproducible?

Keep four values per event: the raw value exactly as extracted, the offset or zone rule used to interpret it, the corrected UTC value, and the ID of the correction applied. A buyer or auditor can then undo or revise any correction without going back to the source system.

Write the corrected value in an unambiguous format. RFC 9557, which updates RFC 3339, says Z indicates a UTC time whose local offset is unknown, while +00:00 indicates that UTC is the preferred reference point; it replaces the older -00:00 convention. In databases, note that PostgreSQL stores timestamp with time zone values internally as UTC, so the original offset has to be kept in its own column if local time matters for the analysis.

Why does a correction report make an event log worth licensing?

Event logs that come with an inventory of their clocks, a correction table and confidence labels are worth more to AI and process mining teams than larger logs with unexplained orderings, because the buyer can trust the sequence. Companies that license such logs through SourceX get the rights review, contract, preparation, delivery and payment handled for them.

Very large logs do not need to move to SourceX at all: they can stay in the seller's own storage or ship on encrypted drives. Before any of that, a brief questionnaire about the company, answered without exporting a single log, shows whether it qualifies.

Frequently asked questions

What is clock skew in an event log?

It is the difference between the clocks of the systems that recorded the events. When logs from several systems are merged, skew makes effects appear before their causes. It is distinct from time zone errors, which shift whole sources by fixed amounts, though both show up as out-of-order events.

Can I fix clock skew by sorting on timestamps after converting to UTC?

No. Converting to UTC fixes zone and offset problems, not differences between clocks. You still need anchor pairs whose order is certain to measure each system's offset, and some ties will remain that only sequence numbers or links can resolve.

How many anchor pairs do I need?

Enough in each period to bound the correction from both directions, if possible. Hold some pairs back to test the correction, and report the interval width. A one-sided estimate should be labelled as such.

Should the corrected timestamps replace the original ones?

No. Keep the raw value, the interpretation rule, the corrected value and the correction ID side by side. That makes every correction reversible and auditable.

What if a system only records when a step finished?

Then its timestamps cannot show when work started or whether steps overlapped. Label the order as completion order, use lifecycle start and complete events where any source has them, and lower the case's confidence.

Sources

  • Lamport's paper shows that happened-before is only a partial ordering because it is sometimes impossible to say which of two events occurred first. Source
  • The Windows Time service adjusts the clock rate to converge, and sets the clock directly when the skew is too large to correct that way. Source
  • NTP calculations assume a symmetrical network path, and asymmetrical paths reduce synchronization accuracy. Source
  • PostgreSQL ignores time zone indications for timestamp without time zone and stores timestamp with time zone values internally as UTC. Source
  • Python's datetime fold attribute disambiguates wall times in the repeated interval when clocks roll back. Source
  • RFC 9557 updates RFC 3339 so that Z indicates UTC with an unknown local offset and +00:00 indicates UTC as the preferred reference point. Source
  • The XES standard defines event-level time:timestamp and lifecycle:transition attributes, with transitions such as start and complete. Source

See if your company qualifies

A short company assessment. No data uploads are needed.

See if your company qualifies