# Calculation of time in hours of a value remains in a certain abounds

**URL:** <https://community.grafana.com/t/calculation-of-time-in-hours-of-a-value-remains-in-a-certain-abounds/100570>\
**Category:** Dashboards\
**Created:** [August 6, 2023, 9:50am UTC](https://community.grafana.com/t/calculation-of-time-in-hours-of-a-value-remains-in-a-certain-abounds/100570 "2023-08-06T09:50:51Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![superboss9](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/superboss9/32/12036_2.png) [@superboss9](https://community.grafana.com/u/superboss9)\
**Post date:** [August 6, 2023, 9:50am UTC](https://community.grafana.com/t/calculation-of-time-in-hours-of-a-value-remains-in-a-certain-abounds/100570/1 "2023-08-06T09:50:51Z")

</div>

I use Grafana OSS variant together with Timeseries DB - Influx DB 2.x version.

I have some timeseries of temperature values from a number of sensors. And the same plot. Everything works fine but I’d like to automate a procedure of calculation of a sensor data kept between two values.

For example, I need to have information how long each of sensors provide me with values above 60 and below 70 C in **used time window**. I need to have it in the same window preferable at the legend side.

Is it possible?

For example, here is a plot driven by two sensors:

 ![image](https://us1.discourse-cdn.com/grafana/original/3X/5/3/53fc0626b7a52ecfe61f2cc19ae83790f69d8527.png)  
The time windows is 24 h and I would like to see how long the sensors were between 60 and 70 C

The query is very simple in my case:

```auto
from(bucket: "Prod")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["iServer"] == "Prdn")
  |> filter(fn: (r) => r["iProbe"] == "Hell_2_1" or r["iProbe"] == "Hell_2_2")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> yield(name: "mean")

```

---

<div class="post-metadata">

**Author:** ![grant2](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/grant2/32/42281_2.png) [@grant2](https://community.grafana.com/u/grant2)\
**Post date:** [August 6, 2023, 12:43pm UTC](https://community.grafana.com/t/calculation-of-time-in-hours-of-a-value-remains-in-a-certain-abounds/100570/2 "2023-08-06T12:43:08Z")

</div>

Hi @superboss9

I think the [elapsed()](https://docs.influxdata.com/flux/v0.x/stdlib/universe/elapsed/#Copyright) function will do what you need.

I worked up an example in my test data to filter out those records between 60 and 70, calculate the elapsed time in fractional hours, and then sum to get the total. You could create a Stat Panel next to your time series with this function. It would correspond to the same 24h time window.

```auto
from(bucket: "Bucket1")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "WeatherData")
  |> filter(fn: (r) => r["_field"] == "OutdoorTemp")
  |> filter(fn: (r) => r["_value"] >= 60.0 and r["_value"] <= 70.0)
  |> elapsed(unit: 1s)
  |> map(fn: (r) => ({ r with elapsedFloat: float(v: r.elapsed)/3600.0 })) // convert seconds to hours
  |> sum(column: "elapsedFloat")
  |> yield(name: "hours-between-60-and-70")

```

 ![image](https://us1.discourse-cdn.com/grafana/original/3X/f/e/fe04e0e4c05a42baa3d32968b0f2f06d345318a9.png)

---

<div class="post-metadata">

**Author:** ![superboss9](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/superboss9/32/12036_2.png) [@superboss9](https://community.grafana.com/u/superboss9)\
**Post date:** [August 6, 2023, 1:36pm UTC](https://community.grafana.com/t/calculation-of-time-in-hours-of-a-value-remains-in-a-certain-abounds/100570/3 "2023-08-06T13:36:43Z")

</div>

As far as I understand - you suggest to get the result in a separate panel (dashboard), Am I correct?  
How do you think - is it possible to incorporate the sum data into the legend accordingly to each of the sensors?

---

<div class="post-metadata">

**Author:** ![grant2](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/grant2/32/42281_2.png) [@grant2](https://community.grafana.com/u/grant2)\
**Post date:** [August 6, 2023, 4:51pm UTC](https://community.grafana.com/t/calculation-of-time-in-hours-of-a-value-remains-in-a-certain-abounds/100570/4 "2023-08-06T16:51:37Z")

</div>

Correct. I do not know of any way to feed a custom value or query value into the legend. Seems you can only choose First, Last, Min, Max, etc.  
 ![image](https://us1.discourse-cdn.com/grafana/original/3X/4/c/4c43fd2f75e6c8ade065a295e79bc15488d5cdf2.png)

---

<div class="post-metadata">

**Author:** ![superboss9](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/superboss9/32/12036_2.png) [@superboss9](https://community.grafana.com/u/superboss9)\
**Post date:** [August 7, 2023, 6:34am UTC](https://community.grafana.com/t/calculation-of-time-in-hours-of-a-value-remains-in-a-certain-abounds/100570/5 "2023-08-07T06:34:40Z")

</div>

Thank you very much.  
In general it works well. But if I need to calculate two values in one query. For example, in the first example we calculated hours between 60 and 70, but what if I need to calculate also hours for from 70 to 80?  
I’m a little bit lost with Influx query language.

The second problem is in data naming:

 ![image](https://us1.discourse-cdn.com/grafana/original/3X/d/a/da8d4455269c0480daf02b37f23d9fb93cc1c464.png)

Here I receive a set of data in calculated hours but I don’t know which sensor gave which results because the information about the source of sensor is omitted in the query. The same not understanding how to work with Influx QL (probably).

---

<div class="post-metadata">

**Author:** ![superboss9](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/superboss9/32/12036_2.png) [@superboss9](https://community.grafana.com/u/superboss9)\
**Post date:** [August 10, 2023, 7:17am UTC](https://community.grafana.com/t/calculation-of-time-in-hours-of-a-value-remains-in-a-certain-abounds/100570/6 "2023-08-10T07:17:49Z")

</div>

I’ve added to panels with Stat Panel and it brings the correct results except one thing:

 ![image](https://us1.discourse-cdn.com/grafana/original/3X/f/9/f972c778f83f84eabe8df5df2a7ce1ef1d19c565.png)  
It correctly calculates sum value of a sensor being in bounds between 60 and 70. But if I have an interruption of this chain at the example there is another process that is \>70 and after it I have a cooling process and the value falls down to windows between 60 and 70 then it calculates the entire value as 60…

Reading manual of Influx - [StateDuration](https://docs.influxdata.com/flux/v0.x/stdlib/universe/stateduration) should help but I can’t get results with it.

---

<div class="post-metadata">

**Author:** ![superboss9](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/superboss9/32/12036_2.png) [@superboss9](https://community.grafana.com/u/superboss9)\
**Post date:** [August 13, 2023, 9:36am UTC](https://community.grafana.com/t/calculation-of-time-in-hours-of-a-value-remains-in-a-certain-abounds/100570/7 "2023-08-13T09:36:31Z")

</div>

Manage to produce the following Influx query:

```auto
  from(bucket: "Production")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["iServer"] == "Production")
  |> filter(fn: (r) => r["iProbe"] == "Hell_1_1" or r["iProbe"] == "Hell_1_2" or r["iProbe"] == "Hell_1_3" or r["iProbe"] == "Hell_1_4" or r["iProbe"] == "Hell_1_5" or r["iProbe"] == "Hell_1_6")
  |> stateDuration(fn: (r) => r["_value"]>=60.0 and r["_value"]<=65.0, column: "keep_60")
  |> keep(columns: ["iProbe", "keep_60", "keep_60h"])
  |> highestMax(n:6, column: "keep_60", groupColumns: ["iProbe"])
  |> sort(columns: ["iProbe"])
  |> map(fn: (r) => ({ r with keep_60h: float(v: r["keep_60"])/3600.0 })) // convert seconds to hours
  |> yield(name: "Hell_1")

```

It returns a table that contains correct values. Not I’m not able to push it into Stat Panel 🤣

PS. Finally fixed the panel and everything works as it should be:  
 ![image](https://us1.discourse-cdn.com/grafana/original/3X/4/2/426c10bbdad888c3ca1468c8e988db4e0a285636.png)

PSS. Had to add filtering of spikes with this  
`|> aggregateWindow(every: 5m, fn: median, column: "_value")`  
That removes the spikes and they do not devide a region into two (or more) regions.