Calculating event duration from boolean values?

,
1

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
2

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
3

@larsks @dgnorton

Just in case someone was asleep since Sep 2017, we have a solution in Feb 2023 using Flux. See here.