Storing state changes and query duration of a state

Hi,

We are currently taking a look at Druid as storage solution for our IoT data. Some of our values are observations of state rather than numeric metrics. It looks something like:

__time, value
10, ACTIVE
12, ACTIVE
15, ACTIVE
17, INACTIVE
19, INACTIVE
20, ACTIVE
25, INACTIVE

``

The assumption is, that the state changes at the time of the first observed value that is different from the previous recorded value. Any subsequent record that is of the same value is just another observation.

We would like to query the total duration that a device was in a specific state. Like:

SELECT condition, count(*) as duration
FROM (SELECT time_bucket(‘1 second’, __time, 10, 30) AS bucket,
last_observation_carry_forward(last(value)) AS condition
FROM observations
GROUP BY 1) AS bucket_cond
GROUP BY 1;

``

which would count the number of seconds (time_bucket) from __time=10 to __time=30 by taking the last observation of each second and fills in the last observed value if there is a gap.

Does Druid support queries like this?

Best

Jochen

Just to clarify, I am interested in the capability to measure time from state change to state change and aggregate the sum of the time by state. It does not have to be accomplished with the exact query and functions below (which are similar to the one TimescaleDB offers).

Jochen