Elapsed time between rows

Calculate the time gap between consecutive events. Useful for detecting delays, measuring inter-arrival times, or identifying gaps in data streams.

Problem

You want to know how much time passed between each row and the previous one, for example to spot gaps in a data feed or measure event frequency.

Solution

SELECT
  timestamp,
  lag(timestamp) OVER (ORDER BY timestamp) AS prev_timestamp,
  datediff('T', timestamp, lag(timestamp) OVER (ORDER BY timestamp)) AS elapsed_millis
FROM trades
WHERE symbol = 'BTC-USDT'
  AND timestamp IN '$today'
LIMIT 20;

The datediff('T', timestamp, prev_timestamp) function returns the difference in milliseconds. Change the unit to control precision:

Raw timestamp subtraction

If you subtract timestamps directly instead of using datediff, the result is in the native resolution of the column (microseconds for TIMESTAMP, nanoseconds for TIMESTAMP_NS):

SELECT
  timestamp,
  timestamp - lag(timestamp) OVER (ORDER BY timestamp) AS elapsed_micros
FROM trades
WHERE symbol = 'BTC-USDT'
  AND timestamp IN '$today'
LIMIT 20;

:::note TIMESTAMP vs TIMESTAMP_NS The trades table uses TIMESTAMP (microsecond precision), so subtraction gives microseconds. Tables like fx_trades use TIMESTAMP_NS (nanosecond precision), where subtraction gives nanoseconds. :::

:::info Related documentation

• lag() function
• datediff() function
• Designated timestamp :::