The sensors below worked fine until I added this required bits:
GROUP BY time(1m) fill(linear)
Now they won’t work anymore with the addition (unavailable). Am I adding it in the wrong place?
sensor:
- platform: influxdb
host: a0d7b954-influxdb
port: 8086
database: homeassistant
username: !secret influxdb_user
password: !secret influxdb_pass
scan_interval: 600 # every 10m don't need it updated more often than the sensors used
queries:
- name: 'Kyl (estimated watertemp middle)'
field: mean_value
where: '"entity_id" = ''kitchen_fridge_temperature'' AND time > now() - 240m GROUP BY time(1m) fill(linear))'
group_function: last
database: homeassistant
measurement: '(SELECT moving_average("value",230) AS "mean_value" FROM "homeassistant"."autogen"."°C"' # was 12
value_template: '{{ (value | float - 0.9) | round(1) }}'
unit_of_measurement: °C
- name: 'Frys (average top shelf)'
field: mean_value
where: '"entity_id" = ''kitchen_freezer_top_temperature'' AND time > now() - 70m GROUP BY time(1m) fill(linear))'
group_function: last
database: homeassistant
measurement: '(SELECT moving_average("value",60) AS "mean_value" FROM "homeassistant"."autogen"."°C"' # was 6
value_template: '{{ value | round(1) }}'
unit_of_measurement: °C
The query inside Grafana is:
SELECT moving_average(mean("value"), 60) FROM "°C" WHERE ("entity_id" = 'kitchen_freezer_top_temperature') AND $timeFilter GROUP BY time(1m) fill(linear)
From the log:
InfluxDB database is not accessible due to 'aggregate function required inside the call to moving_average'. Please check that the database, username and password are correct and that the specified user has the correct permissions set.
influxdb: Error on device update!
Traceback (most recent call last):
File "/usr/src/homeassistant/homeassistant/helpers/template.py", line 1984, in forgiving_float_filter
return float(value)
^^^^^^^^^^^^
ValueError: could not convert string to float: 'None'
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "/usr/src/homeassistant/homeassistant/helpers/entity_platform.py", line 573, in _async_add_entity
await entity.async_device_update(warning=False)
File "/usr/src/homeassistant/homeassistant/helpers/entity.py", line 1214, in async_device_update
await hass.async_add_executor_job(self.update)
File "/usr/local/lib/python3.11/concurrent/futures/thread.py", line 58, in run
result = self.fn(*self.args, **self.kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/influxdb/sensor.py", line 252, in update
value = self._value_template.render_with_possible_json_value(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/helpers/template.py", line 717, in render_with_possible_json_value
).result()
^^^^^^^^
File "/usr/local/lib/python3.11/concurrent/futures/_base.py", line 456, in result
return self.__get_result()
^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/concurrent/futures/_base.py", line 401, in __get_result
raise self._exception
File "/usr/src/homeassistant/homeassistant/util/async_.py", line 48, in run_callback
future.set_result(callback(*args))
^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/helpers/template.py", line 746, in async_render_with_possible_json_value
return _render_with_context(self.template, compiled, **variables).strip()
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/helpers/template.py", line 2305, in _render_with_context
return template.render(**kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/jinja2/environment.py", line 1301, in render
self.environment.handle_exception()
File "/usr/local/lib/python3.11/site-packages/jinja2/environment.py", line 936, in handle_exception
raise rewrite_traceback_stack(source=source)
File "<template>", line 1, in top-level template code
File "/usr/src/homeassistant/homeassistant/helpers/template.py", line 1987, in forgiving_float_filter
raise_no_default("float", value)
File "/usr/src/homeassistant/homeassistant/helpers/template.py", line 1625, in raise_no_default
raise ValueError(
ValueError: Template error: float got invalid input 'None' when rendering template '{{ (value | float - 0.9) | round(1) }}' but no default was specified