Versions:
duration_in() ToolkitTimescaleDB Toolkit functions are available under Timescale Community Edition. They are automatically included with Timescale Cloud. Click to learn more.ExperimentalExperimental features are not suitable for production environments. They are included under the TimescaleDB experimental schema. Click to learn more.
Toolkit
TimescaleDB Toolkit functions are available under Timescale Community Edition. They are automatically included with Timescale Cloud. Click to learn more.
Experimental
Experimental features are not suitable for production environments. They are included under the TimescaleDB experimental schema. Click to learn more.
Use this function to report the total duration for a given state in a state aggregate.
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.
| Name | Type | Description |
|---|---|---|
state | TEXT | State to query |
aggregate | stateagg | Previously created aggregate |
Returns
| Column | Type | Description |
|---|---|---|
duration_in | BIGINT | An object storing the total time in microseconds spent in the target state. |
Sample usage
This example creates a simple test table:
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');You can query this table for the time spent in the running state, like this:
SELECT toolkit_experimental.duration_in('running', toolkit_experimental.state_agg(time, state)) FROM states;Which gives the result:
duration_in
--------------
338400000000Found an issue on this page?
Report an issue!