> ## 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.

# duration_in()

> Calculate the total time spent in a given state from a state aggregate

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

Calculate the total time spent in a state from a state aggregate. If you need to interpolate missing values across time
bucket boundaries, use [`interpolated_duration_in`][interpolated_duration_in].

## 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.

If you prefer to see the result in seconds, [`EXTRACT`][extract] the epoch from the returned result.

```sql theme={"dark"}
SET timezone TO 'UTC';
CREATE TABLE states(time TIMESTAMPTZ, state TEXT);
INSERT INTO states VALUES
  ('1-1-2020 10:00', 'starting'),
  ('1-1-2020 10:30', 'running'),
  ('1-3-2020 16:00', 'error'),
  ('1-3-2020 18:30', 'starting'),
  ('1-3-2020 19:30', 'running'),
  ('1-5-2020 12:00', 'stopping');

SELECT duration_in(
  state_agg(time, state),
  'running'
) FROM states;
```

Returns:

```
duration_in
---------------
3 days 22:00:00
```

## Arguments

The syntax is:

```sql theme={"dark"}
duration_in(
  agg StateAgg,
  state {TEXT | BIGINT}
  [, start TIMESTAMPTZ]
  [, interval INTERVAL]
) RETURNS INTERVAL
```

| Name     | Type           | Default | Required | Description                                                                                         |
| -------- | -------------- | ------- | -------- | --------------------------------------------------------------------------------------------------- |
| agg      | StateAgg       | -       | ✔        | A state aggregate created with [`state_agg`][state_agg]                                             |
| state    | TEXT \| BIGINT | -       | ✔        | The state to query                                                                                  |
| start    | TIMESTAMPTZ    | -       |          | If specified, only the time in the state after this time is returned                                |
| interval | INTERVAL       | -       |          | If specified, only the time in the state from the start time to the end of the interval is returned |

## Returns

| Column       | Type     | Description                                                                                      |
| ------------ | -------- | ------------------------------------------------------------------------------------------------ |
| duration\_in | INTERVAL | The time spent in the given state. Displayed in `days`, `hh:mm:ss`, or a combination of the two. |

[extract]: https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT

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

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

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