Hi, I’m new to grafana and unfortunately couldn’t find any help yet related to my topic. First of all I’m using grafana version 6.4.3 with influxdb version 1.7.9.
I have a reed sensor that stores the state of the sensor in the Influxdb every minute. The state is either 0 or 1. I already have a graph (see picture) where I can see what the state is over the selected interval. In addition, I want to get the total number of hours and minutes over the interval where the status is 0 and display it in a singlestat panel. Is that possible somehow? I would appreciate some help, as I have no idea how to do this.
Thanks for your help. That did the job! Below is the statement that worked for me. There are sometimes gaps in the data (NULL values) because the sensor sometimes doesn’t write the status to influxdb. With “fill(previous)” I could close this gap.
SELECT ABS(SUM(“state”)-1) AS “Status” FROM “xxx” WHERE (“area” = ‘xxx’) AND $timeFilter GROUP BY time(1m) fill(previous).
or to get seconds and use the unit duration hh:mm:ss in the singlestat panel.
SELECT ABS(SUM(“state”)-1)*60 AS “Status” FROM “xxx” WHERE (“area” = ‘xxx’) AND $timeFilter GROUP BY time(1m) fill(previous)
In my opinion, it works well. I checked 3-4 different dates and times and only in one of the examples the Singlestat showed me a minute difference. All the others were good. I added an example (6:59 to 12:44). The time difference seems to be correct.
Thanks for the tip with the discrete panel. I will try that one as well.
SELECT ABS(SUM(“state”)-1) AS “Status” FROM “xxx” WHERE (“area” = ‘xxx’) AND $timeFilter GROUP BY time(1m) fill(previous).
With this query, you are summing up all the values from the status field and then doing -1. By this how you are getting the time when the “status” is 0 instead of getting the sum of 1’s from the specified time interval.
I tried with this query, I am getting the sum value not the time.
please help me in understanding this. I am confused with the query and the result.
