Why Your Timestamps Disagree

Two systems record the same event and give it different times. The historian says the pump tripped at 14:32:06, the SCADA event log says 14:32:04, the maintenance system's work order says 14:31. Somebody is asked to explain the discrepancy and the investigation stalls, because before anyone can reason about what happened they have to establish what happened first. This is one of the most common defects in industrial data, and it is almost never a broken sensor.

The usual cause is that nobody decided which clock the timestamp comes from. A single reading passes at least four of them: the controller or sensor that took the sample, the gateway that received it, the broker or API that ingested it, and the database that wrote the row. Each has a clock; each is capable of stamping the record. If the pipeline is not explicit about which stamp is authoritative, the answer will be whichever component the last developer touched, and it will differ between two paths carrying the same data.

The rule is simple to state: the sample time is recorded as close to the measurement as possible and travels with the data, unchanged, forever. Everything downstream that also wants a timestamp adds its own field. Keeping receipt time alongside sample time is not redundancy — the difference between them is the transport latency, which is a genuinely useful diagnostic, and it is unrecoverable once one of the two has been overwritten.

That only helps if the clock at the edge is right, which brings up the accuracy question. NTP across a good local network gets you within a few milliseconds; across a congested WAN or a cellular link, tens to hundreds of milliseconds is realistic, and asymmetric paths bias the result in ways NTP cannot detect. That is entirely adequate for trending a tank level and entirely inadequate for sequence-of-events analysis, where the whole point is to establish which of two protection devices operated first. If ordering at the millisecond level matters, the answer is PTP with hardware timestamping in the network interface, which reaches sub-microsecond on a properly configured switch fabric — and, importantly, it is the hardware timestamping that buys the accuracy, not the protocol name in the datasheet.

Cheap clocks drift, and the arithmetic is easy to underestimate. A typical crystal is specified at twenty parts per million, which is one point seven seconds a day, about fifty seconds a month. A device that syncs on boot and never again is a device whose data is a minute out by the end of the month, and the error accumulates smoothly enough that nobody notices it happening. Worse are the devices with no battery-backed real-time clock at all: they come up at the Unix epoch, or at whatever time they were last told, and everything they emit between power-on and the first successful sync carries a timestamp from 1970 or from last Tuesday. Set a time-valid flag, hold telemetry until the clock is disciplined, or mark those records explicitly — but do not let them into the historian looking like ordinary data.

Time zones cause a different class of error, and the fix is not controversial: store UTC everywhere, convert only for display, and never write a local time into a database column without an offset. The failure mode people forget is the ambiguous hour where daylight saving ends, in which local times repeat. An hour of data ordered by local timestamp is scrambled and, worse, half of it may collide with existing rows on a primary key that assumed local time was unique. Any site that observes a summer-time shift generates this exact defect twice a year, and a site whose government changes the rule generates it once, without warning, when the operating system's zone database is updated on some machines and not others.

Interval arithmetic deserves its own warning. Durations computed by subtracting two wall-clock readings are wrong whenever the clock is stepped between them, which is precisely what an NTP correction does after a device has drifted. The result can be negative. Use a monotonic clock for elapsed time and a wall clock for absolute time, and never mix them, because a run-time counter that occasionally reports a negative interval will be blamed on the sensor for months.

Resolution is a quieter trap. If the source samples at ten hertz and the store keeps whole seconds, ten records collapse onto one timestamp, and any consumer that assumes timestamp uniqueness silently keeps one of them. If ordering within the second matters, either store the sub-second component or carry a per-device sequence number as a tie-breaker. A sequence number is worth having regardless: it survives clock corrections, it makes duplicates from retried transmissions detectable, and it is the only reliable way to prove nothing was lost during a network outage.

What this adds up to is a short set of decisions, best made once and written down. One time source per site, with a documented accuracy target that is honestly derived from what the data is used for. Sample time stamped at the source, in UTC, carried unmodified end to end. Receipt time kept as a separate field. A sequence number per device. A time-valid flag for the window before synchronisation. And an alarm on clock skew, so that a drifting device announces itself rather than waiting to be discovered in the middle of an incident investigation, which is the most expensive possible moment to learn that your timestamps were never trustworthy.