interpolated_state_timeline() - Tiger Data

Since 1.15.0 Get a timeline of all states, showing each time a state is entered and exited. Unlike state_timeline, you can use this function across multiple state aggregates that cover different time buckets. Any missing values at the time bucket boundaries are interpolated from adjacent state aggregates.

Samples

Given state aggregates bucketed by 1-minute intervals, interpolate the states at the bucket boundaries and get the history of all states. To perform the interpolation, the LAG and LEAD functions are used to get the previous and next state aggregates.

SELECT
    bucket,
    (interpolated_state_timeline(
        summary,
        bucket,
        '15 min',
        LAG(summary) OVER (ORDER by bucket)
    )).*
FROM (
    SELECT
        time_bucket('1 min'::interval, ts) AS bucket,
        state_agg(ts, state) AS summary
    FROM states_test
    GROUP BY time_bucket('1 min'::interval, ts)
) t;

Returns:

         bucket         | state |       start_time       |        end_time
------------------------+-------+------------------------+------------------------
 2020-01-01 00:00:00+00 | START | 2020-01-01 00:00:00+00 | 2020-01-01 00:00:11+00
 2020-01-01 00:00:00+00 | OK    | 2020-01-01 00:00:11+00 | 2020-01-01 00:15:00+00
 2020-01-01 00:01:00+00 | ERROR | 2020-01-01 00:01:00+00 | 2020-01-01 00:01:03+00
 2020-01-01 00:01:00+00 | OK    | 2020-01-01 00:01:03+00 | 2020-01-01 00:16:00+00
 2020-01-01 00:02:00+00 | STOP  | 2020-01-01 00:02:00+00 | 2020-01-01 00:17:00+00

Arguments

The syntax is:

interpolated_state_timeline(
    agg StateAgg,
    start TIMESTAMPTZ,
    interval INTERVAL,
    [, prev StateAgg]
) RETURNS (TIMESTAMPTZ, TIMESTAMPTZ)

interpolated_state_int_timeline(
    agg StateAgg,
    start TIMESTAMPTZ,
    interval INTERVAL,
    [, prev StateAgg]
) RETURNS (TIMESTAMPTZ, TIMESTAMPTZ)

Name	Type	Default	Required	Description
agg	StateAgg	-	✔	A state aggregate created with `state_agg`
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
state	TEXT	BIGINT	A state found in the state aggregate
start_time	TIMESTAMPTZ	The time when the state started (inclusive)
end_time	TIMESTAMPTZ	The time when the state ended (exclusive)