Your timestamps came from two different clocks

Something trips. By the time you are in the room, the plant is down, and the question on the table is the one that always comes first.

What happened first?

Did the pump stop and then the pressure fall, or did the pressure fall and the pump protect itself? One of those is a mechanical failure and the other is the system working as designed, and the difference between them is the order of two rows in a database.

So it is worth knowing where the timestamps on those rows come from.

The short answer: not from one clock#

On the estate I run, readings reach the historian by more than one route. I measured what each route puts in the time column.

One path records the moment the instrument produced the value.

The other records the moment a piece of software got round to asking.

Both land in a column called ts. Same name, same format, same table shape. Nothing anywhere marks which kind of time a given row contains, and any query that pulls from both is quietly comparing one against the other.

How far apart do they drift?#

I run a signal that ticks once a second, deliberately, so it can be used as a ruler.

On the first path, consecutive readings are exactly one second apart — not approximately, exactly, with no variation at all across the whole measurement. It is reporting the instrument's own sense of when the value existed.

On the second, consecutive readings average 1.024 seconds apart.

That sounds like nothing. It is 24 milliseconds per tick, and it never corrects itself, so it accumulates: about a minute and a half of drift every hour.

The cause is mundane and extremely common. The software samples a value, does its work, and then sleeps for one second before going round again — so every cycle is one second plus however long the work took. Nothing measures the total. Nothing pulls it back. A loop written that way is always slow, and always slow by an amount nobody chose.

Why this matters more than it sounds#

For trending, it does not matter. A drifting clock draws a perfectly good curve.

It matters when you compare things.

Sequence of events. If two readings came from paths whose clocks disagree, the order you see is not necessarily the order that happened. Reverse two rows and the story reverses with them.

Anything joined on time. Batch records against process data, energy against production, a vibration event against a trip. Joining on a timestamp assumes the timestamps mean the same thing.

Evidence. A regulator, an insurer or a customer is shown a time. Nobody asks which clock it came from, and there is usually no way to answer if they did.

The thing nobody checks#

While measuring this I looked at what the plant equipment does about time synchronisation — whether anything is telling those machines what the time is.

Across a full day of watching every conversation on the network, the plant equipment asked nothing, ever. Not once. The only machines checking the time were the infrastructure underneath them.

I should be honest about the limit of that result on my own rig: my sites run in a way that makes them share an underlying clock, so they cannot actually drift apart from each other. What I measured is what the network shows — that no equipment was asking. On real hardware, separate boxes with nothing telling them the time, that same silence is the condition where the clocks quietly diverge, and each one timestamps its readings with its own private idea of when now is.

What I would check on Monday#

Ask what your timestamps mean. For each path into your historian: is that the moment the instrument had the value, or the moment something wrote it down? Most people have never been asked and have to go and look.

Check whether both are stored. The good answer is two columns — when it happened, and when it landed. Older systems often have this. Newer pipelines often dropped it, because moving a value is the obvious job and the second timestamp looks redundant right up until you need it.

Ask what is telling your equipment the time, and confirm it by looking rather than by assuming. It is a five-minute check and the answer is frequently nothing.

Then look at a sequence-of-events reconstruction you already trust, and ask whether the rows in it came from the same clock. If they did not, the order is a hypothesis rather than a record.

None of this is exotic and none of it needs buying. It is the difference between a timestamp being a fact about the plant and a timestamp being a note somebody's software made about itself.

The measurements, the interval distributions and the exact loop that produces the drift are in the lab notes. What the same estate does about the identity on a reading, as opposed to the time, is in a companion piece.