Measuring which clock stamped each row, on two paths into the same historian

The companion lab asked what a row in a historian says about where it came from. This one asks when — specifically, whose clock produced the number in the time column, and whether two paths into the same database agree about it.

The instrument is a signal that advances once per second on every site, which turns interval measurement into a ruler: if the timestamps are faithful to the source, consecutive rows are exactly 1000 ms apart. Anything else is the measurement path adding its own signature.

Method#

Both paths write to the same TimescaleDB instance on the same host. Query the interval between consecutive rows of the same tag on each, over the same window:

with d as (
  select site, ts, lag(ts) over (partition by site order by ts) as prev
  from <table>
  where tag_path like '%Ramp0%' and ts > now() - interval '5 minutes'
)
select site, count(*) n,
       round(avg (extract(epoch from ts-prev))*1000)     avg_ms,
       round(stddev(extract(epoch from ts-prev))*1000,1) jitter_ms,
       round(min (extract(epoch from ts-prev))*1000)     min_ms,
       round(max (extract(epoch from ts-prev))*1000)     max_ms
from d where prev is not null group by site;

No instrumentation is added to either path. The measurement is entirely of what was already stored.

Result#

The licensed path's distribution has no width at all. Every interval is the same integer. That is only possible if the timestamp originates with the tag's own scan, not with any later act of publishing or receiving — network transit and batching would both leave a signature, and there is none.

The open path's distribution is centred 24 ms late with a tail out to 1054 ms. That is the shape of a wall-clock reading taken at whatever moment a loop got to it.

The cause, in four lines#

The open path's collector is about eighty lines of Python. The relevant part:

while True:
    now = time.time()
    ...                       # build and enqueue the sample
    time.sleep(1.0 / RATE_HZ)

The sleep is a fixed duration placed after the work. The cycle is therefore work + 1.000 s, the error is whatever the work costs, and because nothing measures elapsed time against a deadline, the error is never repaid. It accumulates linearly.

At the measured 24 ms per cycle that is 86 s/hour — the nominal 1 Hz signal is really 0.977 Hz. The fix is the standard one: schedule against a monotonic deadline that advances by a fixed step, and sleep for the remainder, so a slow cycle is absorbed rather than carried forward.

This is not a criticism of the language or the agent. It is the default shape of almost every sampling loop written quickly, in any language, and it is invisible until something is used as a ruler.

Where the two kinds of time end up#

Both paths write a column named ts, of the same type, in tables that are otherwise column-for-column identical. Neither records an ingest timestamp alongside the event timestamp. An earlier pipeline on this same estate did — it carries both ts and inserted_at — and that distinction was not carried into either newer path.

The practical consequence is that a query joining the two tables on time is comparing a plant timestamp against a collector timestamp, and there is no column that would let it know.

What the network says about time synchronisation#

Separately, from 21.8 hours of packet capture on the estate bridge covering every inter-host conversation: counting time-synchronisation traffic by zone, the SCADA equipment sent none at all. The hypervisor sent 304 packets, the platform host 15, the plant zones zero.

Stated limitation. The sites on this rig are containers sharing a host kernel clock, so they cannot drift relative to each other regardless of what the network shows. This measurement is therefore about what was asked for, not about observed divergence, and it should not be reported as the latter. On separate physical hardware the same silence is the precondition for divergence rather than evidence of it.

What transfers#

• Use a known-rate signal as a ruler. Interval statistics on data you already store will tell you whether a path is faithful to source time, with no new instrumentation.
• Zero variance is itself a finding. A distribution with no width cannot have been produced downstream of a network.
• sleep(period) after work is always slow, by an amount nobody chose, and never recovers. Schedule against a deadline.
• Store both timestamps. When it happened and when it landed answer different questions, and the second one is free at write time and impossible to reconstruct afterwards.
• A shared column name is not a shared meaning. Two ts columns of the same type can hold different quantities, and nothing in the schema will say so.
• Do not report what the network asked for as what the clocks did. Those are different claims and only one of them was measured here.

The practitioner argument built on these numbers is in the companion post. The identity half of the same question — what a row says about where it came from — is in the provenance lab.