> ## Documentation Index
> Fetch the complete documentation index at: https://mintlify-poc.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# interpolated_state_timeline()

> Get a timeline of all states from a state aggregate, interpolating values at time bucket boundaries

<Icon icon="tag" iconType="duotone" /> Since [1.15.0][toolkit-1.15.0]

Get a timeline of all states, showing each time a state is entered and exited.

Unlike [`state_timeline`][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.

```sql theme={"dark"}
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:

```sql theme={"dark"}
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`][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)   |                                      |

[state_agg]: /api-reference/timescaledb-toolkit/state-tracking/state_agg/state_agg

[state_timeline]: /api-reference/timescaledb-toolkit/state-tracking/state_agg/state_timeline

[toolkit-1.15.0]: https://github.com/timescale/timescaledb-toolkit/releases/tag/1.15.0