Getting a moving average from influxdb

Configuration
1

I’m currently getting an average for 1 hours back for an influxdb sensor. That works ok, but the average sometimes jumps crazy when there are some missing datapoints. I’d really prefer a moving average instead for x datapoints back, like in grafana, but I can’t get the query to work.

For the current solution with a mean average, the query is:
SELECT mean("value")-0.825 AS "mean_value" FROM "homeassistant"."autogen"."°C" WHERE time > now() - 1h AND "entity_id"='frys_2_hogsta_hyllan_temperature'

For the moving average I’ve tried:
SELECT moving_average("value",6) AS "mean_value" FROM "homeassistant"."autogen"."°C" WHERE time > now() - 1h AND "entity_id"='frys_2_hogsta_hyllan_temperature'

But it won’t return anything. I’d like a moving average of the last 6 data points, which is roughly 1 hour (10min / update). What am I doing wrong?

EDIT:
This query below returns data, that is correct, but it returns 3 values and I only want the LAST one returned. Also I can’t see why I have to look back 3 hours to get anything from a 6 datapoint value? 2h back won’t return nada.

SELECT moving_average("value",6) AS "mean_value" FROM "homeassistant"."autogen"."°C" WHERE time > now() - 3h AND "entity_id"='frys_2_hogsta_hyllan_temperature'
2

You’ll need subqueries, i.e

SELECT LAST(mean_value) from (SELECT moving_average...

Are you positive you have more then 6 datapoints over the last 3h?

3

Thanks, I got it working like this:

SELECT LAST(mean_value) from (SELECT moving_average("value",6) AS "mean_value" FROM "homeassistant"."autogen"."°C" WHERE time > now() - 2h AND "entity_id"='frys_2_hogsta_hyllan_temperature')

But then I hit another roadblock. How do I translate this into an influxdb sensor, that currently looked like this:

     - name: 'Frys (average top shelf)'
        field: value
        where: '"entity_id" = ''frys_2_hogsta_hyllan_temperature'' AND time > now() - 1h'
        group_function: mean
        database: homeassistant
        measurement: '"homeassistant"."autogen"."°C"'
        value_template: '{{ value | round(1) }}'
        unit_of_measurement: °C
4

measurement: is the FROM clause which is sent to InfluxDB, so you can try to put the subquery there, with a group_function of last and a field of “mean_value”

An alternative is to use a REST sensor and use the Influxdb REST API.

See Using HA or HACS graphs w/ InfluxDB data in HA? - #6 by koying where I use that in another context.

5

To say this is messy is the understatement of the year, but this WORKS: Thanks for the help!

      - name: 'Frys (average top shelf)'
        field: mean_value
        where: '"entity_id" = ''frys_2_hogsta_hyllan_temperature'' AND time > now() - 2h)'
        group_function: last
        database: homeassistant
        measurement: '(SELECT moving_average("value",6) AS "mean_value" FROM "homeassistant"."autogen"."°C"'
        value_template: '{{ value | round(1) }}'
        unit_of_measurement: °C