Search code examples
grafanainfluxdb

In InfluxDB: I am unable to convert an expression to an integer

The intention is that Grafana 6.6.1 will query this from InfluxDb 1.8.2

I want to use the moving_average() function based on the time period $timeFilter that is selected in the dashboard.

I have tried a few queries in the CLI. In the following snippets, time > '2020-10-27' will be substituted by $timeFilter in Grafana.

query 1:

SELECT  round(count("Temp")*0.1)  FROM "Consumption" WHERE time > '2020-10-27'

Outputs:

name: Consumption
time                           round
----                           -----
2020-10-27T00:00:00.000000001Z 7

query 2:

SELECT moving_average("Temp", 7) FROM "Consumption" WHERE time > '2020-10-27'

Outputs:

name: Consumption
time                 moving_average
----                 --------------
2020-11-03T00:00:00Z 7.535714285714286
2020-11-04T00:00:00Z 7.184528571428571
2020-11-05T00:00:00Z 6.833342857142857
2020-11-06T00:00:00Z 7.303585714285714
.....etc

However, I am unable to combine these queries

query 3:

SELECT moving_average("Temp", round(count("Temp")*0.1)) FROM "Consumption" WHERE time > '2020-10-27'

Outputs:

ERR: second argument for moving_average must be an integer, got *influxql.Call

So I guess round() does not return an integer. Any advice on how to solve this?

Edit

bonus question: How do I make sure the second argument in moving_average() never goes below 1. If selecting a shorter timespan, then it shall be 1

Solution

  • Well I got it working now.

    In Grafana I added a variable to the dashboard.

    Variables

    Name: MyRollingAvgValue, Type: Query, Hide: Variable, Refrech: on time range change

    And the query itself for the variable:

    SELECT mean(MyVal::integer) As MyRollingAvg FROM 
( SELECT 1+round(count("Temp")*0.1) as MyVal FROM "Consumption" WHERE $timeFilter )

    Panel in the dashboard

    Querys tab

    SELECT moving_average("Temp", $MyRollingAvgValue) FROM "Consumption" WHERE $timeFilter

    ALIAS BY: °C ($MyRollingAvgValue day avg)

    Visualization tab

    • Add series override

    alias or regex --> /.*day avg/