Determine hours per month during which a threshold was exceeded


  • What are you trying to achieve?
    I try to determine the hours my Heating ran per month. I have a recording that shows the state (0/1). This, I aggregate this way:

  • How are you trying to achieve it?

SELECT sum(“value”) / 60 AS “Betriebsstunden pro Monat” FROM “Haustechnik.UVR.AnforderungKessel” WHERE $timeFilter GROUP BY time(30d) fill(previous)

  • What happened?
    I am not sure how this handles the fact that there is not a datapoint each hour - but only when the value changes?

  • What did you expect to happen?
    That every hour where the value was 1 is counted as 1 - even if “previous” value must be considered.
    In addition, this state (0/1) may not be accurate. Thus, I would as an alternative use the time that the Temperature is > 55 degrees.
    Here, “sum” does not work, as I would not sum up the hours but the degrees.

  • Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.

Best regards,

Hi @henfri

I cannot tell what your datasource is, but if you can use Flux (with InfluxDB), this should help you accomplish what you want. It uses the elapsed() function which returns the time between subsequent records.

Ok, thanks. I will give it a try.