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.

  1. interpolated_state_periods(
  2. state [TEXT | BIGINT],
  3. tws [StateAgg | TimelineAgg],
  4. start TIMESTAMPTZ,
  5. interval INTERVAL,
  6. prev [StateAgg | TimelineAgg],
  7. next [StateAgg | TimelineAgg]
  8. ) 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

NameTypeDescription
stateTEXT or BIGINTState to query
aggregateTimelineAggPreviously created state_agg aggregate
startTIMESTAMPTZThe start of the interval which this function should cover (if there is a preceeding point)
intervalINTERVALThe length of the interval

Optional arguments

NameTypeDescription
prevTimelineAggThe TimelineAgg 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.
nextTimelineAggThe TimelineAgg from the following interval, used to interpolate the value at start + interval. If NULL, the last timestamp in aggregate is used as the end of the interval.

Returns

ColumnTypeDescription
start_timeTIMESTAMPTZThe time the state started at (inclusive)
end_timeTIMESTAMPTZThe time the state ended at (exclusive)

Sample usage

Getting the interpolated history of states in a timeline aggregate:

  1. SELECT
  2. bucket,
  3. (toolkit_experimental.interpolated_state_periods(
  4. 'OK',
  5. summary,
  6. bucket,
  7. '15 min',
  8. LAG(summary) OVER (ORDER by bucket),
  9. LEAD(summary) OVER (ORDER by bucket)
  10. )).*
  11. FROM (
  12. SELECT
  13. time_bucket('1 min'::interval, ts) AS bucket,
  14. toolkit_experimental.timeline_agg(ts, state) AS summary
  15. FROM states_test
  16. GROUP BY time_bucket('1 min'::interval, ts)
  17. ) t;

Example output:

  1. bucket | start_time | end_time
  2. ------------------------+------------------------+------------------------
  3. 2020-01-01 00:00:00+00 | 2020-01-01 00:00:11+00 | 2020-01-01 00:15:00+00
  4. 2020-01-01 00:01:00+00 | 2020-01-01 00:01:03+00 | 2020-01-01 00:16:00+00