Skip to content

interpolated_duration_in()

Calculate the total time spent in a given state from a state aggregate, interpolating values at time bucket boundaries

Since 1.15.0

Calculate the total duration in a given state. Unlike duration_in, you can use this function across multiple state aggregates that cover different time buckets. Any missing values at the start of a time bucket are interpolated from the previous state aggregate.

Create a test table that tracks when a system switches between starting, running, and error states. Query the table for the time spent in the running state. To perform the interpolation, the LAG function is used to get the previous state aggregate.

If you prefer to see the result in seconds, EXTRACT the epoch from the returned result.

SET timezone TO 'UTC';
CREATE TABLE states(time TIMESTAMPTZ, state TEXT);
INSERT INTO states VALUES
('2020-01-01 10:00', 'starting'),
('2020-01-01 10:30', 'running'),
('2020-01-02 16:00', 'error'),
('2020-01-03 18:30', 'starting'),
('2020-01-03 19:30', 'running'),
('2020-01-04 12:00', 'stopping');
SELECT
time,
interpolated_duration_in(
agg,
'running',
time,
'1 day',
LAG(agg) OVER (ORDER BY time)
) FROM (
SELECT
time_bucket('1 day', time) as time,
state_agg(time, state) as agg
FROM
states
GROUP BY time_bucket('1 day', time)
) s;

Returns:

time | interpolated_duration_in
------------------------+--------------------------
2020-01-01 00:00:00+00 | 13:30:00
2020-01-02 00:00:00+00 | 16:00:00
2020-01-03 00:00:00+00 | 04:30:00
2020-01-04 00:00:00+00 | 12:00:00

The syntax is:

interpolated_duration_in(
agg StateAgg,
state {TEXT | BIGINT},
start TIMESTAMPTZ,
interval INTERVAL,
prev StateAgg
) RETURNS INTERVAL
NameTypeDefaultRequiredDescription
aggStateAgg-✔A state aggregate created with state_agg. Must not be NULL
stateTEXT | BIGINT-✔The state to query
startTIMESTAMPTZ-✔The start of the interval to be calculated
intervalINTERVAL-✔The length of the interval to be calculated
prevStateAgg-✔The state aggregate from the prior interval, used to interpolate the value at start. Pass NULL if there is none; nothing before the first timestamp in agg is then counted. start must be at or after the last timestamp in prev
ColumnTypeDescription
interpolated_duration_inINTERVALThe total time spent in the queried state. Displayed as days, hh:mm:ss, or a combination of the two.