The state_agg aggregate measures the amount of time spent in each distinct value of a state field. It is designed to work with a relatively small number of states and might not perform well on queries where states are mostly distinct across rows.


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

tsTIMESTAMPTZColumn of timestamps
valueTEXTColumn of states


stateaggstateaggAn object storing the total time spent in each state.

Sample usage

This example creates a state aggregate over a status column in a devices table, with a timestamp column time.

SELECT toolkit_experimental.state_agg(time, status) FROM devices;

Found an issue on this page?

Report an issue!


Related Content