Platforms · Software · Providers · Markets
A trade happens once. By the time it reaches a database it carries three or four different times, none of which are wrong, and all of which measure something slightly different. Most reconciliation arguments are really arguments about which clock somebody meant.
The clocks in a normal path
| Timestamp | Set by | Typical gap to the previous one |
|---|---|---|
| Matching engine time | The venue, at the match | Reference point |
| Feed publication time | The venue's distribution layer | 10 to 500 microseconds |
| Gateway receive time | Your broker or vendor | 1 to 50ms over public networks |
| Local receive time | Your own process | 0.1 to 20ms, plus scheduling jitter |
| Database write time | Your storage layer | Anything, and not a market event |
The last row is the one that causes trouble, because it is the easiest to record and the least meaningful. Ordering events by their database write time produces a sequence that is usually right and occasionally, under load, badly wrong.
Where the disagreement comes from
- Different clocks. The venue, the gateway and your host each keep their own, disciplined to varying standards.
- Different events. Match, publish, receive and store are four separate moments, not four readings of one.
- Different resolutions. A feed in milliseconds and a log in microseconds will never agree exactly, and rounding hides the direction of the error.
- Daylight saving and venue conventions. Rarer than it was, still not extinct, and always discovered on the day the clocks change.
What to do about it
- Store every timestamp you are given, each in its own column, in UTC.
- Record your own receive time with a monotonic source, not wall-clock time.
- Monitor the offset between your host and a reference, and alert on drift rather than checking it after an incident.
- Pick one clock as the ordering key, write that choice down, and make every report say which one it used.
- When two systems disagree, compare the same clock in both before assuming either is broken.
The goal is not to make the clocks agree, because they measure different things and never will. It is to know which one each number came from, so that a discrepancy becomes a fact about the path rather than a mystery about the data.
Frequently Asked Questions
Which timestamp should I store?
All of them, in separate columns. Storing one is a decision you cannot undo, and the cost of three bigint columns is trivial next to the cost of rebuilding history because you kept the wrong clock.
How far apart are exchange and local time typically?
For a retail connection over the public internet, commonly 20 to 150ms, with occasional excursions far beyond that. Co-located, it falls to microseconds. The spread matters more than the figure.
Is NTP good enough?
For strategies measured in seconds, yes, typically within a few milliseconds. For anything measured in microseconds you want PTP and hardware timestamping, and you want to monitor the offset rather than assume it.
