# Calculating event duration from boolean values?

I have data in an influxdb database from a door sensor. This is a boolean sensor (either the door is open (value is `false`) or it is closed (value is `true`)), and the table looks like:

``````name: door
--------------
time			value
1506026143659488953	true
1506026183699139512	false
1506026751433484237	true
1506026761473122666	false
1506043848850764808	true
1506043887602743375	false
``````

I would like to calculate how long the door was open in a given period of time. The ELAPSED function gets me close, but I’m not sure how to either (a) restrict it to only those intervals for which the intitial value is `false`, or (b) identify “open” intervals from the output of something like `select elapsed(value, 1s) from door`.

I was hoping I could do something like:

``````select elapsed(value, 1s), first(value) from door
``````

But that doesn’t get me anything useful:

``````name: door
--------------
time			elapsed	first
0				true
1506026183699139512	40
1506026751433484237	567
1506026761473122666	10
1506043848850764808	17087
1506043887602743375	38
``````

I was hoping for something more along the lines of:

``````name: door
--------------
time			elapsed	first
1506026183699139512	40	true
1506026751433484237	567	false
1506026761473122666	10	true
1506043848850764808	17087	false
1506043887602743375	38	true
``````

Short of extracting the data myself and processing it in e.g. python, is there any way to do this via an influxdb query?

2 Likes

The best solution for this is probably to use Kapacitor with a script something like this…

``````stream
|from()
.database('door')
.measurement('door')
|stateDuration(lambda: "value" == FALSE)
.unit(1s)
|influxDBOut()
.database('door')
.retentionPolicy('autogen')
.measurement('door_open')
``````

However, as noted by @michael on this thread, you’ll need to sample and write the sate of the door more often than just on state changes in order for it to calculate state duration.

1 Like