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
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.
| Cause | Symptom in the merged log | How to detect it | Correction |
|---|---|---|---|
| Local time stored without an offset | Whole sources shifted by whole hours | Offsets between systems cluster at hour multiples | Reinterpret with the source's documented zone; keep the rule used |
| Daylight saving transitions | Duplicated or missing local hour twice a year | Violations concentrate on transition dates | Resolve the repeated hour with sequence data; flag what stays ambiguous |
| Clock stepped by time sync | Sudden jump, sometimes backward, within one source | Change point in the offset series for that source | Piecewise correction split at the step |
| Slow drift between syncs | Offset that grows steadily over days or weeks | Trend in the per-day offset estimate | Per-period correction, never one constant |
| Entry time recorded instead of occurrence time | Events appear minutes or days after their effects | Large positive gaps with no fixed pattern | Do not shift; label as entry time and lower confidence |
| Coarse precision | Many events share the same second, minute or date | High share of exact ties within a case | Order 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.
| Report section | Contents | Method | Pass condition |
|---|---|---|---|
| Source inventory | System, table, column type, stored offset, precision, occurrence or entry time | Schema review and documentation | Every timestamp column classified |
| Normalization rules | Zone assumed per source, daylight saving handling, raw value retained | Documented rule per source | Raw value and rule kept beside the UTC value |
| Anchor pairs | Pair types with the business reason the order is certain, count per system pair and period | Join on shared identifiers | Enough pairs per period to estimate an interval |
| Offset estimates | Correction interval per source and period, change points | Bounds from anchor pairs, change point review | Interval width stated; steps explained |
| Violations before and after | Share of anchor pairs out of order before and after correction | Recount on held-out anchor pairs | Violations fall on pairs not used to estimate |
| Corrections applied | Constant, piecewise or none, with the value per source and period | Versioned correction table | Every corrected event points to one correction ID |
| Residual ambiguity | Ties and orders that remain uncertain | Count per case | Ambiguous orderings labelled concurrent, not forced |
| Reconciliation | Event and case counts per source before and after merge | Totals compared to source extracts | No events lost or duplicated |
| Case confidence | High, medium or low per reconstructed case with reason | Rule-based from the sections above | Every 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.