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​

Elapsed time between consecutive tradesDemo this query
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:

UnitDescription
's'Seconds
'T'Milliseconds
'U'Microseconds

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):

Raw difference in microsecondsDemo this query
SELECT
  timestamp,
  timestamp - lag(timestamp) OVER (ORDER BY timestamp) AS elapsed_micros
FROM trades
WHERE symbol = 'BTC-USDT'
  AND timestamp IN '$today'
LIMIT 20;
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.

Related documentation