Skip to content

interpolated_downtime()

Get the total time dead from a heartbeat aggregate and predecessor

Since 1.15.0

Calculate downtime from a heartbeat aggregate, taking the heartbeat aggregate from the preceding interval to interpolate values at the boundary. This checks when the last heartbeat in the predecessor was received and makes sure not to consider the heartbeat interval after that time as unhealthy, even if it extends into the current aggregate prior to the first heartbeat.

Given a table called liveness containing weekly heartbeat aggregates in column health with timestamp column date, use this command to get the total interpolated downtime of the system during the week of Jan 9, 2022.

SELECT interpolated_downtime(health, pred)
FROM (
SELECT date, health, LAG(health) OVER (ORDER BY date) AS pred
FROM liveness
) s
WHERE date = '01-9-2022 UTC'

Returns:

interpolated_downtime
---------------------
00:03:55

The syntax is:

interpolated_downtime(
agg HeartbeatAgg,
pred HeartbeatAgg
) RETURNS INTERVAL
NameTypeDefaultRequiredDescription
aggHeartbeatAgg-✔A heartbeat aggregate to get the liveness data from
predHeartbeatAgg-✔The heartbeat aggregate for the interval before the one being measured. It must end at or before the start of agg. Pass NULL if there is none
ColumnTypeDescription
interpolated_downtimeINTERVALThe sum of all the unhealthy ranges in the aggregate, excluding those covered by the last heartbeat of the previous interval.