Total hour/minutes of a time interval

Grafana Stat Panel
1

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!

07
072664×1596 364 KB

2

You need a simple math -1 and then sum aggregation + absolute value of the result

Concept:

SELECT ABS(SUM(state-1)) FROM…

See Influxdb doc to understand used math function.

3

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)

4

SUM(“state”)-1 doesn’t look good to me - it sums all ON (1) states and then -1. Am I right?

If you really don’t need singlestat, then https://grafana.com/grafana/plugins/natel-discrete-panel can be a good choice for this ON/OFF types of metrics.

5

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.

13
132254×736 46.1 KB

6

The idea with the discrete panel was great and I replaced my graph. Thanks!

8

field type of “status” is Boolean or Integer?

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.

9

Have you got resolution ?