> ## 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="flask" /> Early access <Icon icon="tag" iconType="duotone" /> Since [1.5.0][toolkit-1.5.0]

Calculate the total time spent in the given 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 toolkit_experimental.duration_in(
  toolkit_experimental.compact_state_agg(time, state),
  'running'
) FROM states;
```

Returns:

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

The syntax is:

```sql theme={"dark"}
duration_in(
  agg CompactStateAgg,
  state {TEXT | BIGINT}
) RETURNS INTERVAL
```

| Name  | Type            | Default | Required | Description                                                             |
| ----- | --------------- | ------- | -------- | ----------------------------------------------------------------------- |
| agg   | CompactStateAgg | -       | ✔        | A state aggregate created with [`compact_state_agg`][compact_state_agg] |
| state | TEXT \| BIGINT  | -       | ✔        | The state to query                                                      |

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

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

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

[interpolated_duration_in]: /api-reference/timescaledb-toolkit/state-tracking/compact_state_agg/interpolated_duration_in#arguments

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