interpolated_duration_in() - Tiger Data

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 multiple time buckets. Any missing values at the time bucket boundaries are interpolated from adjacent state aggregates.

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. Use LAG and LEAD to get the neighboring aggregates for interpolation. If you prefer to see the result in seconds, EXTRACT the epoch from the returned result.

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

Arguments

The syntax is:

interpolated_duration_in(
  agg StateAgg,
  state {TEXT | BIGINT},
  start TIMESTAMPTZ,
  interval INTERVAL
  [, prev StateAgg]
) RETURNS INTERVAL

Name	Type	Default	Required	Description
agg	StateAgg	-	✔	A state aggregate created with `state_agg`
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	StateAgg	-		The state aggregate from the prior interval, used to interpolate the value at `start`. If `NULL`, the first timestamp in `aggregate` is used as the start of the interval

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.