SQL Event Gaps: LAG, Stable Ordering, and Explicit Window Frames

Find the missing heartbeat, not the busiest host

A fleet agent emits a heartbeat every thirty seconds. An aggregate query says each host sent roughly the expected number of events today, yet one host disappeared for eight minutes and later flushed its buffered records. A daily count hides gaps. You need neighboring event times within each host, and you must decide whether the question concerns heartbeat occurrence or collector arrival.

For this investigation, use occurrence time and preserve receipt time for a separate shipping-delay query. A large occurrence gap suggests no recorded heartbeat in that interval; it does not by itself prove the host was down. A clock jump, dropped telemetry, or a producer restart can generate similar evidence.

PostgreSQL: subtract instants

This query targets PostgreSQL with occurred_at stored as timestamptz and event_id unique. PostgreSQL stores the instant, not the original zone name. Its display depends on the session timezone, so UTC output makes the investigation easier to compare across clients.

SET TIME ZONE 'UTC';

WITH ordered AS (
  SELECT host_id, event_id, occurred_at,
         LAG(occurred_at) OVER (
           PARTITION BY host_id
           ORDER BY occurred_at, event_id
         ) AS previous_at
  FROM heartbeat_events
  WHERE occurred_at >= TIMESTAMPTZ '2026-10-06 00:00:00+00'
    AND occurred_at <  TIMESTAMPTZ '2026-10-07 00:00:00+00'
)
SELECT host_id, event_id, previous_at, occurred_at,
       EXTRACT(EPOCH FROM occurred_at - previous_at) AS gap_seconds
FROM ordered
WHERE occurred_at - previous_at > INTERVAL '90 seconds'
ORDER BY host_id, occurred_at, event_id;

The first row in each partition has no predecessor and produces NULL. The filter excludes that row naturally. More subtly, the midnight filter is evaluated before LAG, so it removes yesterday's predecessor. If the first heartbeat after midnight is part of a cross-boundary gap, this query misses it. Include an adequate lookback or fetch one predecessor per host, compute the window, and only then filter displayed rows to the reporting day.

An event_id tie-breaker stabilizes equal timestamps. It does not prove which event actually happened first. The final ORDER BY is still necessary because window ordering defines evaluation, not the presentation order of the result set.

Frames change running aggregates

For an accumulated byte count, write the intended frame explicitly:

SELECT host_id, event_id, occurred_at,
       SUM(payload_bytes) OVER (
         PARTITION BY host_id
         ORDER BY occurred_at, event_id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS bytes_so_far
FROM heartbeat_events;

Default ordered frames commonly include peers under RANGE semantics. With duplicate ordering values, that can make a running aggregate jump across several rows together. An explicit ROWS frame plus a unique ordering key gives row-by-row accumulation. LAG is an offset function rather than a frame aggregate; changing the frame does not turn it into a different predecessor lookup.

SQLite: publish the stored unit

SQLite 3.25+ supports window functions, but it does not provide PostgreSQL's timestamptz type or interval subtraction syntax. For an integer occurred_ms column, keep arithmetic in the published unit:

WITH ordered AS (
  SELECT host_id, event_id, occurred_ms,
         LAG(occurred_ms) OVER (
           PARTITION BY host_id ORDER BY occurred_ms, event_id
         ) AS previous_ms
  FROM heartbeat_events
)
SELECT host_id, event_id,
       (occurred_ms - previous_ms) / 1000.0 AS gap_seconds
FROM ordered
WHERE occurred_ms - previous_ms > 90000
ORDER BY host_id, occurred_ms, event_id;

Do not divide the stored value by 1,000 twice or rely on automatic interpretation of timestamp text. Enforce integer storage and a product-appropriate range at ingestion, and use bound parameters for user-supplied dates or host IDs.

Check correctness before tuning

Seed two hosts, equal timestamps, a cross-midnight gap, and a late arrival. Verify each expected predecessor before analyzing indexes. An index beginning with host and occurrence time may help ordered host queries, but your actual filter and plan determine its value. Use the SQL utility for SQLite experiments and the timestamp utility to inspect units; PostgreSQL-specific examples belong in a PostgreSQL environment, not a SQLite sandbox.