Aggregate sensor data from Home Assistant in InfluxDB and indicate missing data out of time range

  • What Grafana version and what operating system are you using?

    • Grafana v10.3.0-145892
    • InfluxDB v2.7.3
    • Inside Docker Container
  • What are you trying to achieve?

    • Group Sensor Data from Home Assistant using their name/entity_id and by type (e.g. Temperature, Humidity, Light Brightness, …)
    • Use the friendly_name as an alias to show inside the Legend.
    • Show data that is not within the specified time range of Grafana, so that data from sensors that don’t update frequently is also shown and there are no gaps at the front (and end) of the graph.
  • How are you trying to achieve it?

Using this InfluxQL query:

SELECT "value" FROM "%" WHERE ("entity_id"::tag =~ /humidity/) AND $timeFilter GROUP BY "friendly_name"::tag
  • What happened?

I can’t find a way to implement a function that adds the previous data outside the time interval for each sensor individually.
For example, this Group By function doesn’t work: GROUP BY clause | InfluxDB OSS v2 Documentation

SELECT mean("value") FROM "%" WHERE ("entity_id"::tag =~ /humidity/) AND $timeFilter GROUP BY "friendly_name"::tag, time(5m)

And with the fill function, it doesn’t work at all:

SELECT mean("value") FROM "%" WHERE ("entity_id"::tag =~ /humidity/) AND $timeFilter GROUP BY "friendly_name"::tag, time(5m), fill(linear)
  • What did you expect to happen?

To add missing data on the left, and maybe a function to smooth out the green jumps.