Time spent in state (elapsed)

InfluxDB 2 Dashboards
1

I’m looking at getting some times spent in a state (for my house alarm just for giggles).

Here’s the past 12 Hours:

 select * from alarm_state_arm_disarm where time > now() - 12h   tz('Pacific/Auckland')

    name: alarm_state_arm_disarm
    time                          value
    ----                          -----
    2018-08-06T01:00:00.299+12:00 1
    2018-08-06T02:00:00.202+12:00 1
    2018-08-06T03:00:00.28+12:00  1
    2018-08-06T04:00:00.291+12:00 1
    2018-08-06T05:00:00.135+12:00 1
    2018-08-06T05:59:01.846+12:00 0
    2018-08-06T06:00:00.337+12:00 0
    2018-08-06T06:10:48.54+12:00  1
    2018-08-06T07:00:00.141+12:00 1
    2018-08-06T07:41:34.715+12:00 0
    2018-08-06T08:00:00.178+12:00 0
    2018-08-06T08:32:30.755+12:00 1
    2018-08-06T09:00:00.144+12:00 1
    2018-08-06T09:55:55.929+12:00 0
    2018-08-06T10:00:00.384+12:00 0
    2018-08-06T11:00:00.127+12:00 0
    2018-08-06T11:02:22.359+12:00 1
    2018-08-06T11:21:24.356+12:00 0
    2018-08-06T12:00:00.204+12:00 0

So it seems to post every hour and on change.

So using the elapsed in the 1 state for the past 12 hours I get:

select elapsed(value,1m) from alarm_state_arm_disarm where time > now() - 12h  and value = 1 tz('Pacific/Auckland')
name: alarm_state_arm_disarm
time                          elapsed
----                          -------
2018-08-06T02:00:00.202+12:00 59
2018-08-06T03:00:00.28+12:00  60
2018-08-06T04:00:00.291+12:00 60
2018-08-06T05:00:00.135+12:00 59
2018-08-06T06:10:48.54+12:00  70
2018-08-06T07:00:00.141+12:00 49
2018-08-06T08:32:30.755+12:00 92
2018-08-06T09:00:00.144+12:00 27
2018-08-06T11:02:22.359+12:00 122

So if I sum these I’m not sure if they’re right. The 27 Mins at 9am I think is right, I don’t get the 5am and ad 6 start. I disarmed the alarm at 6am and would have armed it again at 6:10 so that doesn’t seem to work.

I just tried the sum,

select sum(elapsed(value,1m)) from alarm_state_arm_disarm where time > now() - 12h group by time(1d)  tz('Pacific/Auckland')

But it doesn’t seem to like it.

Is this a use case I should be able to work with?