interpolated_duration_in()
Calculate the total time spent in a given state from a state aggregate, interpolating values at time bucket boundaries
Calculate the total duration in the 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.
start must not be after the first timestamp in agg, and start + interval must not be before the last timestamp
in agg. Otherwise the function raises an error.
Samples
Section titled “Samples”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, toolkit_experimental.interpolated_duration_in( agg, 'running', time, '1 day', LAG(agg) OVER (ORDER BY time)) FROM ( SELECT time_bucket('1 day', time) as time, toolkit_experimental.compact_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:002020-01-02 00:00:00+00 | 16:00:002020-01-03 00:00:00+00 | 04:30:002020-01-04 00:00:00+00 | 12:00:00Arguments
Section titled “Arguments”The syntax is:
interpolated_duration_in( agg CompactStateAgg, state {TEXT | BIGINT}, start TIMESTAMPTZ, interval INTERVAL, prev CompactStateAgg) RETURNS INTERVAL| Name | Type | Default | Required | Description |
|---|---|---|---|---|
| agg | CompactStateAgg | - | ✔ | A state aggregate created with compact_state_agg. Must not be NULL |
| state | TEXT | BIGINT | - | ✔ | The state to query |
| start | TIMESTAMPTZ | - | ✔ | The start of the interval to be calculated |
| interval | INTERVAL | - | ✔ | The length of the interval to be calculated |
| prev | CompactStateAgg | - | ✔ | 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 |
Returns
Section titled “Returns”| Column | Type | Description |
|---|---|---|
| interpolated_duration_in | INTERVAL | The total time spent in the queried state. Displayed as days, hh:mm:ss, or a combination of the two. |