How to align timestamps across data sources

Aligning timestamps across feeds takes explicit timezone offsets, careful daylight-saving handling, awareness of exchange clocks, and one declared bar-boundary convention. RFC 3339 gives unambiguous formatting and Unix time a common numeric base.

Accurately aligning timestamps across multiple market data sources requires four separate decisions to be made explicit and consistent: the timezone of the recorded clock time, the daylight saving rule in force at that moment, the exchange or venue clock that stamped the event, and the bar-boundary convention used to group events into intervals.

A timestamp is not fully specified unless its offset from Coordinated Universal Time is known. RFC 3339 defines a date-time format that records a full date, a full time, and either the UTC designator Z or a numeric offset such as plus two hours or minus five hours. This matters because the same wall-clock time can refer to different instants when used in different offsets.

A worked conversion shows the mechanism. If one feed records 2024-07-01T12:00:00Z and a venue operates at UTC plus two hours during daylight saving time, then the venue local time is 14:00:00 because 12:00:00 plus 2:00:00 equals 14:00:00; if the same venue is at UTC plus one hour outside daylight saving time, then 2024-01-01T12:00:00Z converts to 13:00:00 because 12:00:00 plus 1:00:00 equals 13:00:00. The join bug appears when one source is normalized to UTC and another source keeps venue local time without the offset, because identical text strings no longer identify identical instants.

Unix time gives a numeric representation of time as the count of seconds since 1970-01-01 00:00:00 UTC. That numeric base is useful because two timestamps that refer to the same instant should map to the same integer second after conversion. If they do not, the mismatch is not cosmetic formatting. It is a real disagreement about time.

Daylight saving shifts create a second class of bug because the offset itself changes over the calendar. A pipeline that hard-codes one offset for a venue will be correct for part of the year and wrong for the rest. In the worked example above, treating a summer timestamp as if the offset were plus one hour instead of plus two hours moves the record back by one hour because 14:00:00 minus 13:00:00 equals 1:00:00, which is large enough to place trades into the wrong session or the wrong bar.

Exchange-specific clock differences create a third class of bug even when every timestamp is expressed in UTC. Two feeds can use different exchange clocks or can stamp records at different points in their own processing chain. A simple worked example shows the effect: if Feed A stamps an event at 09:30:00 and Feed B stamps the same event at 09:30:02, then the difference is 2 seconds because 09:30:02 minus 09:30:00 equals 2 seconds. If one-minute bars are defined on boundaries ending exactly at 09:31:00, a trade at 09:30:59 from Feed A belongs to the 09:30:00 to 09:31:00 bar, but the same trade shifted forward by 2 seconds in Feed B becomes 09:31:01 and falls into the next bar because 09:30:59 plus 2 seconds equals 09:31:01. A join on bar timestamp will then mismatch the records even though both feeds describe the same underlying market event.

Bar-boundary conventions create a fourth class of bug because interval membership depends on whether the end of the interval is included. An inclusive-exclusive convention assigns times t such that the start is included and the end is excluded, written in words as start less than or equal to t and t less than end. An inclusive-inclusive convention assigns times t such that both boundaries are included, written in words as start less than or equal to t and t less than or equal to end. The overlap problem can be shown in one sentence: if Bar 1 is from 10:00:00 to 10:01:00 and Bar 2 is from 10:01:00 to 10:02:00, then under inclusive-inclusive both bars contain the boundary timestamp 10:01:00 so the overlap count is 1 because the shared set is {10:01:00}, while under inclusive-exclusive the overlap count is 0 because the first bar stops just before 10:01:00 and the second bar starts at 10:01:00. A join bug appears when one source labels a bar by its opening boundary and another labels a bar by its closing boundary, or when one source includes the right edge and another excludes it.

These problems compound when a system joins across venues. A record can be shifted by an incorrect timezone offset, shifted again by a daylight saving assumption, and then assigned to a different bar by a boundary convention that does not match the other feed. The result is a clean-looking table with silent temporal misalignment.

The practical rule is to normalize every event to a single instant representation first, usually UTC or Unix time, and only then assign it to sessions or bars under one declared boundary convention. After normalization, any venue-local display time should be treated as a derived view rather than the join key. That separation prevents formatting choices from being confused with event time and makes clock differences visible as measurable offsets instead of hidden join failures.

For cross-venue datasets, the important point is not just storing timestamps, but storing the semantics of those timestamps. The offset must be explicit, the daylight saving rule must be correct for the date, the exchange clock must be understood as a possible source of seconds-level disagreement, and the bar convention must be fixed before aggregation. Without all four, joins can be wrong even when every timestamp looks valid.

Drafted with AI assistance from cited sources. Reviewed and approved by Sonar Sciences Quant & Research Team.