duration_in()
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.
Required arguments
Name | Type | Description |
---|---|---|
state | TEXT or BIGINT | State to query |
aggregate | StateAgg or TimelineAgg | Previously created aggregate |
Returns
Column | Type | Description |
---|---|---|
duration_in | INTERVAL | The total time spent in the target state. Displayed as days , hh:mm:ss , or a combination of the two. |
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
---------------
3 days 22:00:00
If you prefer to see the result in seconds, EXTRACT the epoch from the returned result.
当前内容版权归 TimescaleDB 或其关联方所有,如需对内容或内容相关联开源项目进行关注与资助,请访问 TimescaleDB .