interpolated_state_periods()

Returns the times in a given state in a timeline aggregate.

Unlike state_periods, 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 TimelineAggs.

interpolated_state_periods(
    state [TEXT | BIGINT],
    tws [StateAgg | TimelineAgg],
    start TIMESTAMPTZ,
    interval INTERVAL,
    prev [StateAgg | TimelineAgg],
    next [StateAgg | TimelineAgg]
) RETURNS (TIMESTAMPTZ, TIMESTAMPTZ)

warning

Experimental features could have bugs. They might not be backwards compatible, and could be removed in future releases. Use these features at your own risk, and do not use any experimental features in production.

Required arguments

Optional arguments

Returns

Sample usage

Getting the interpolated history of states in a timeline aggregate:

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

Example output:

bucket         |       start_time       |        end_time
------------------------+------------------------+------------------------
 2020-01-01 00:00:00+00 | 2020-01-01 00:00:11+00 | 2020-01-01 00:15:00+00
 2020-01-01 00:01:00+00 | 2020-01-01 00:01:03+00 | 2020-01-01 00:16:00+00