Duration of value ; a query question

I have a question about getting the duration of a value, which I can’t wrap my head around. It’s probably a simple thing for seasoned influx/query guru’s, but please spare me ; I’m just leaning to use it all.

Ik have a table as following:

This table records values whenever the state changes of a sensor (“Vrij” means “Free” and “Bezet” means “Occupied”).

What I want to do, is to get the average duration in which the state is “Bezet”. The question is: How does the query looks like to accomplish this?

Does anyone has the answer to this question?

How about adding up all the timestamps for Bezet, adding up all the timestamps
for Vrij, and subtract Bezet from Vrij, then dividing by the number of
instances of Bezet?

For example, suppose your timings were:

Bezet: 00:01:00
Vrij: 00:03:00
Bezet: 01:10:00
Vrij: 01:16:30
Bezet: 01:20:00
Vrij: 01:22:15

Then the sum of Bezet is 02:31:00 and the sum of Vrij is 02:41:45.

Subtract Bezet from Vrij to get 00:10:45, divide by three to get 00:03:35

Check: first period = 00:02:00, second = 00:06:30, last = 00:02:15

Average of those three is 00:03:35

You just need to be sure that you count the same number of Bezet values as you
count Vrij values.

An alternative, of course, would be to change the code which writes the
timestamps into the table to begin with so that it subtracts the previous
Bezet timestamp from the Vrij value now being written, and adds the difference
as a Duration value which you can then average with a simple query.


Thank you Pooh for the answer.

I have a followup question --> What is the best practice of recording and reporting / querying this kind of boolean (on/off, 1/0, Vrij/Bezet) sensor data?

The reason I ask this question is because I was expecting a build in feature/function from a timeseries db such as Influxdb without having to calculate things, therefore I’ve just recorded the values and not the durations.

This question arises as I also want to know (in seconds and in graph form) the duration between two records (e.g. the duration per “Bezet” event between Bezet and the next “Vrij” record).

Maybe you could shed a light on how to do this and what the best practice is?