Market Data Timestamps: SIP vs Exchange Clocks

Market data timestamps na clocks wey dem stamp on one trade print as e dey move from the matching engine wey execute am go the screen wey show am. US equity trade get three of dem for public record, and each one dey answer different question. Sort the same day prints with one clock, then sort am with another, and you get two genuinely different tapes.

The four clocks wey one print pass through

Dem stamp print plenty times as e dey come reach you. Na so e take go:

1. Matching engine time. Na the exact moment wey venue matching engine match two orders. Nobody outside the venue fit read this value directly. Na the true record of when trade happen, and every clock after am na approximation.
2. Participant time, wey dem still call venue or exchange time. Na the stamp wey venue write when e publish print for its own feed, inside the participant_timestamp field. Among all the values wey you fit actually read, this one dey nearest to matching engine.
3. SIP time. Na the stamp wey securities information processor write when print reach consolidated tape, the one official feed wey merge every US equity venue. Na sip_timestamp field be this, and official tape sequence dey follow am. The difference between that feed and venue own feed dey explained for SIP versus direct exchange feeds.
4. Capture time. Na the stamp wey your own network card write when packet land. E no dey appear for vendor record because e describe your own path, no be market. Work on Packet capture and replay dey use this clock completely.

Off-exchange prints get fifth stamp, trf_timestamp, wey show when trade reporting facility receive the report.

Why market data timestamps dey disagree across venues

The gap between venue stamp and consolidated stamp na the time wey print spend for transit and processor queue. E no be one fixed number. Each venue dey different distance from processor, dey use different hardware, and dey behind different queue. The panel below measure this gap for every venue wey print AAPL during fixed half hour on 10 June 2026.

WITH venues AS
(
    SELECT
        toUInt32(id)                             AS exchange_id,
        any(coalesce(nullIf(acronym, ''), name)) AS venue_name
    FROM global_markets.stocks_exchanges
    WHERE asset_class = 'stocks'
    GROUP BY exchange_id
)
SELECT
    if(v.venue_name = '', concat('Venue ', toString(t.exchange)), v.venue_name) AS venue,
    count()                                                                     AS print_count,
    round(quantileDeterministic(0.5)(
        toFloat64(toUnixTimestamp64Nano(t.sip_timestamp)
                - toUnixTimestamp64Nano(t.participant_timestamp)) / 1000,
        toUInt64(t.sequence_number)), 1)                                        AS median_lag_us,
    round(quantileDeterministic(0.99)(
        toFloat64(toUnixTimestamp64Nano(t.sip_timestamp)
                - toUnixTimestamp64Nano(t.participant_timestamp)) / 1000,
        toUInt64(t.sequence_number)), 1)                                        AS p99_lag_us
FROM global_markets.stocks_trades AS t
LEFT JOIN venues AS v ON v.exchange_id = toUInt32(t.exchange)
WHERE t.ticker = 'AAPL'
  AND t.sip_timestamp >= '2026-06-10 14:30:00'
  AND t.sip_timestamp <  '2026-06-10 15:00:00'
  AND ifNull(toUnixTimestamp64Nano(t.trf_timestamp), 0) = 0
GROUP BY venue
HAVING count() >= 200
ORDER BY median_lag_us DESC
LIMIT 15

Across those venues, the widest median gap between venue stamp and consolidated stamp na 346.2 microseconds, for NYSE Arca, Inc.. The tightest venue for the same window run 13.7 microseconds. Na the p99 column you suppose watch well: for that same slowest venue, e reach 420.8 microseconds. Na the tail be this, and median no dey show am.

Which timestamp you suppose use

Four rules cover almost every situation.

• Participant time for microstructure work and event studies. Anything wey measure wetin happen for venue and the order wey e happen belong to venue clock. Order book reconstruction, wey MBO versus MBP order book data talk about, no work with any other clock.
• SIP time for anything wey must reconcile with official tape. Regulatory reporting, best execution review, official open and close, and any figure wey counterparty go check against consolidated record.
• Capture time only to measure your own path. E tell you how long data take reach your machine. E no tell you when trade happen, and two machines no go ever agree on am.
• No mix clocks inside one dataset. Join wey match quotes with one clock against trades with another fit return numbers wey look reasonable, but fail exactly for moments wey matter.

The same prints, sorted two ways

Na sorting dey show the real difference. The panel below take the busiest ten milliseconds of that half hour, keep the first twelve exchange prints in venue order, then rank the same twelve again in consolidated tape order. The table sort by how far each print move between the two rankings.

WITH
    burst AS
    (
        SELECT intDiv(toUnixTimestamp64Nano(participant_timestamp), 10000000) AS slice_10ms
        FROM global_markets.stocks_trades
        WHERE ticker = 'AAPL'
          AND sip_timestamp >= '2026-06-10 14:30:00'
          AND sip_timestamp <  '2026-06-10 15:00:00'
          AND ifNull(toUnixTimestamp64Nano(trf_timestamp), 0) = 0
        GROUP BY slice_10ms
        ORDER BY count() DESC, slice_10ms ASC
        LIMIT 1
    ),
    sample AS
    (
        SELECT
            participant_timestamp,
            sip_timestamp,
            toUInt64(sequence_number) AS seq,
            toUnixTimestamp64Nano(sip_timestamp)
              - toUnixTimestamp64Nano(participant_timestamp) AS lag_ns
        FROM global_markets.stocks_trades
        WHERE ticker = 'AAPL'
          AND sip_timestamp >= '2026-06-10 14:30:00'
          AND sip_timestamp <  '2026-06-10 15:00:00'
          AND ifNull(toUnixTimestamp64Nano(trf_timestamp), 0) = 0
          AND intDiv(toUnixTimestamp64Nano(participant_timestamp), 10000000)
              IN (SELECT slice_10ms FROM burst)
        ORDER BY participant_timestamp ASC, seq ASC
        LIMIT 12
    ),
    ranked AS
    (
        SELECT
            participant_timestamp,
            sip_timestamp,
            lag_ns,
            row_number() OVER (ORDER BY participant_timestamp ASC, seq ASC) AS participant_rank,
            row_number() OVER (ORDER BY sip_timestamp ASC, seq ASC)         AS sip_rank
        FROM sample
    )
SELECT
    concat('P', leftPad(toString(participant_rank), 2, '0')) AS print_label,
    concat(formatDateTime(toTimeZone(participant_timestamp, 'America/New_York'), '%H:%i:%S'), '.',
           leftPad(toString(intDiv(toUnixTimestamp64Nano(participant_timestamp) % 1000000000, 1000)), 6, '0')) AS venue_clock_et,
    concat(formatDateTime(toTimeZone(sip_timestamp, 'America/New_York'), '%H:%i:%S'), '.',
           leftPad(toString(intDiv(toUnixTimestamp64Nano(sip_timestamp) % 1000000000, 1000)), 6, '0'))         AS tape_clock_et,
    participant_rank,
    sip_rank,
    abs(toInt32(sip_rank) - toInt32(participant_rank)) AS places_moved,
    round(lag_ns / 1000, 1)                            AS sip_lag_delta_us
FROM ranked
ORDER BY places_moved DESC, participant_rank ASC

The biggest gap between one print two ranks na 6. That print leave its venue at 10:44:17.160712 and reach tape 305.7 microseconds later, at 10:44:17.161018. E move from position 6 on venue clock to position 12 on tape. Neither ordering wrong. Each one answer different question. Trade sequence study wey use tape clock go read this burst in order wey no venue ever produce, while best execution review wey use venue clock go disagree with official record.

Off-exchange prints dey land far behind the event

Trade wey execute away from exchange, for wholesaler or dark pool, dey report to trade reporting facility instead of matching on public book. The report carry execution time, but e reach tape later. That interval na reporting delay, no be travel time, and e dey many orders of magnitude bigger.

WITH off_exchange AS
(
    SELECT
        (toUnixTimestamp64Nano(sip_timestamp)
       - toUnixTimestamp64Nano(participant_timestamp)) / 1000000.0 AS delay_ms
    FROM global_markets.stocks_trades
    WHERE ticker = 'AAPL'
      AND sip_timestamp >= '2026-06-10 14:30:00'
      AND sip_timestamp <  '2026-06-10 15:00:00'
      AND ifNull(toUnixTimestamp64Nano(trf_timestamp), 0) > 0
)
SELECT
    multiIf(delay_ms <     1, 'under 1 ms',
            delay_ms <    10, '1 to 10 ms',
            delay_ms <   100, '10 to 100 ms',
            delay_ms <  1000, '100 ms to 1 s',
            delay_ms < 10000, '1 s to 10 s',
                              'over 10 s')                       AS reporting_delay,
    count()                                                      AS print_count,
    round(100 * count() / (SELECT count() FROM off_exchange), 2) AS share_pct
FROM off_exchange
GROUP BY reporting_delay
ORDER BY min(delay_ms) ASC

Among off-exchange AAPL prints for that window, 24.8% land inside under 1 ms bucket. The tail reach 1 s to 10 s bucket, with 74 prints inside am. Print wey arrive ten seconds late still carry venue clock time wey show when e actually execute, even as e dey ten seconds downstream inside tape stream. If you sort with tape time, e go look like say e happen for wrong minute. Prints wey dem report outside normal sequence carry sale condition codes wey show this, and na one of the things trade condition codes dey flag.

Why two people fit build different bars from the same trades

Almost every “the data is wrong” ticket dey end here. Bar na bucket of prints, and the stamp wey you use determine the bucket wey each print enter. The panel below count prints wey change bucket when you switch from venue clock to tape clock, across four common bar lengths.

WITH
    prints AS
    (
        SELECT
            toUnixTimestamp64Nano(participant_timestamp) AS venue_ns,
            toUnixTimestamp64Nano(sip_timestamp)         AS tape_ns
        FROM global_markets.stocks_trades
        WHERE ticker = 'AAPL'
          AND sip_timestamp >= '2026-06-10 14:30:00'
          AND sip_timestamp <  '2026-06-10 15:00:00'
    ),
    grids AS
    (
        SELECT arrayJoin([1, 10, 60, 300]) AS bar_seconds
    )
SELECT
    multiIf(bar_seconds =  1, '1 second',
            bar_seconds = 10, '10 seconds',
            bar_seconds = 60, '1 minute',
                              '5 minutes') AS bar_length,
    countIf(intDiv(venue_ns, toInt64(bar_seconds) * 1000000000)
         != intDiv(tape_ns,  toInt64(bar_seconds) * 1000000000)) AS moved_print_count,
    round(100 * countIf(intDiv(venue_ns, toInt64(bar_seconds) * 1000000000)
                     != intDiv(tape_ns,  toInt64(bar_seconds) * 1000000000)) / count(), 3) AS moved_pct
FROM prints
CROSS JOIN grids
GROUP BY bar_seconds
ORDER BY bar_seconds ASC

For 1 second grid, 8.966% of prints for that window enter different bar under the two clocks, 8944 prints altogether. If you extend the bar to 5 minutes, the figure drop to 0.024%. The pattern na mechanical: print change bucket whenever its clock gap cross a boundary, and shorter bars get more boundaries. Two vendors fit both dey correct and still publish different volume for the same minute. how OHLCV bars are built explain the construction step by step.

A nanosecond field no mean nanosecond accuracy

Both stamps arrive as integers with nanosecond resolution. Resolution na wetin field fit express. Accuracy na how close the value dey to true time, and different things determine the two.

Regulatory tolerance, no physics, dey control clock alignment across the industry. FINRA clock synchronization rule require member firms’ business clocks to stay within 50 milliseconds of NIST reference. Exchanges and processors dey run much tighter, using Precision Time Protocol (PTP, standardised as IEEE 1588). PTP distribute reference clock over the same network wey carry data and keep machines within sub microsecond alignment.

Two things follow from this. Inside one organisation stamps, ordering at microsecond resolution get meaning. Across different organisations, difference of a few hundred nanoseconds between two stamps fit dey inside the error bar. If you treat am as real ordering, you dey read noise.

FAQ

Wetin be the difference between SIP timestamp and participant timestamp?

Venue write participant timestamp when e publish trade on its own feed. Consolidated tape processor write SIP timestamp when that trade reach official combined feed. The gap between dem na transit and queueing time. For on-exchange prints, dem measure am in microseconds. For prints wey pass through trade reporting facility, e often reach milliseconds or longer.

Which market data timestamp I suppose use for backtesting?

Use participant timestamp for anything wey model wetin participant fit have seen or done for venue. Use SIP timestamp for anything wey must reconcile with official consolidated record. Whichever one you choose, apply am to every table for the study, including quotes.

Why my one minute bars no match my data provider own?

Clock mismatch na the usual answer. Print wey venue stamp put just before minute boundary fit get tape stamp just after the boundary. This put the same trade inside different bars under the two methods. Late off-exchange prints wey dem report later fit make the difference bigger.

Nanosecond timestamps accurate to nanosecond?

No. The field get nanosecond resolution, but accuracy depend on how well the writing machine clock synchronise. Exchange and processor systems wey use PTP maintain sub microsecond alignment, while broker business clocks get regulatory tolerance of 50 milliseconds. Comparisons wey finer than the tolerance of the less accurate clock no get useful meaning.

Every panel above come with the SQL wey produce am, so you fit see exactly which clock each number come from. To sort your own print window again with another clock and watch how tape change, ask the question in plain English on Strasmore terminal.