Grafana Graph History Card configuration

[m4sis (tony)]

Good morning all,
Under HA, I have a map with the history of my thermostat as in the image, but I cannot reproduce it under grafana in order to have a better history and a better design. And in passing I would like to add a curve of the outside temperature.
someone to refer me?

[sheminasalam]

hi there… I guess you have already installed grafana and any database addon like influx db. What is the exact problem you are facing with grafana?

[mbuscher]

Here is a start, with 3 temperatures pulled from InfluxDB. Still working on converting hvac_action_str to show current_temperature where value is “heating”, which can then be used to show the green shading.

The three queries are

SELECT mean("current_temperature") FROM "autogen"."state" WHERE ("entity_id" = 'forced_air') AND $timeFilter GROUP BY time($__interval) fill(previous)
SELECT mean("temperature") FROM "autogen"."state" WHERE ("entity_id" = 'forced_air') AND $timeFilter GROUP BY time($__interval) fill(previous)
SELECT mean("value") FROM "autogen"."°C" WHERE ("entity_id" = 'local_temperature') AND $timeFilter GROUP BY time($__interval) fill(null)

[mbuscher]

It’s getting there, although I cannot figure out how to show the heating periods with area shading.

The query for the heating is as follows. Note the different types of quotes.

SELECT mean("current_temperature") FROM "autogen"."state" WHERE ("entity_id" = 'forced_air') AND "hvac_action_str"='heating' AND $timeFilter GROUP BY time($__interval) fill(none)

[m4sis (tony)]

Yes influxdb is installed. I don’t have HVAC going back, nor the set temperature I think there must be some handling on HA to do?

[m4sis (tony)]

@mbuscher

FROM "a

how did you get the hvac me i didn’t

[mbuscher]

I assume that you have installed the InfluxDB add-on in HA and that the log shows a successful launch of the app.

• Using the InfluxDB icon in the navigation bar, you’ll at first land on the tab for Alerting.

• Change to the Explore tab.

• Under DB.RetentionPolicy you should see your HA database, e.g. homeassistant.autogen

• Clicking on that DB will show all the measurements it contains. Any sensor with a unit of measurement will have its own measurement, everything else and including state attributes can be found under the state measurement.

• Scroll down to state or find it via the filter next to Measurements & Tags

• Expand state by clicking on the word or triangle

• Under entity_id, find your climate entity. Note that this entity_id is shown without the domain. For example, in my case, climate.forced_air becomes just forced_air. You can use the respective filter to more quickly find your entity of interest.

• Toggle the entity_id on (blue dot)

• Now on the right side you have a long list of Fields. Scroll down to find the field of your interest. In my case I’m looking for the attribute hvac_action, which was copied to InfluxDB as field hvac_action_str

• As soon as I toggle on that field, a query appears.

• Now you know the search criteria for the query you’ll construct in Grafana

[m4sis (tony)]

Thank you for your explanation, I tried well but I can’t see a result like yours, I get a single curve and I don’t know what it corresponds to

[Dougiebabe]

Thanks to the info in this thread I was able to create a very similar graph to the in built HA one but in Grafana.

To do this I had to do the following:

• Follow the instructions above to create the Temperature and Target lines (I created in theGUI)
• Use the following query to create the ‘Heating’ line

SELECT mean("current_temperature") FROM "state" WHERE ("entity_id" = 'kitchen') AND "hvac_action_str"='heating' AND $timeFilter GROUP BY time($__interval) fill(null)

(I changed mbuscher line from temperature to current_temperature otherwise the fill goes over the temperature)

• Create an override and add the following things:
  Fields with Name: NAME OF ALIAS
  Graph Style > Connect null values: Threshold (<15m) - This means the gradient won’t go more than 15min gaps
  Graph Styles ) Line Width: 0
  If you want a gradient then add
  Graph Styles > Fill Opacity: Whatever you like
  Graph Styles > Gradient Mode: Opacity

Then you can change the colours of the lines/fill if you want

Let me know if you think there could be any upgrades!

[clempat (Clement)]

I could as well almost get the same result thanks to this topic. My only problem is the Threshold. My climates can stay without any updates for quite some time.

I will see if adding a keep_alive to my Generic Thermostat will do the trick. But I would be open if anyone come with another solution.

[TTLucian (Lucian)]

Create a template sensor like this:

sensor:
    name: Heating graph
    unique_id: sensor.heating_graph
    state: >
      {% if state_attr('climate.heating', 'hvac_action') == 'heating' %}
        {{state_attr('climate.heating', 'temperature')}}
      {% else %}
        0
      {% endif %}

And add it in the graph. No threshold needed.

[1192410]

I’m having exactly the same question. However, I updated my InfluxDB to v 2.x and it’s only working with Flux. I’m struggeling to transform the query SELECT mean("current_temperature") FROM "state" WHERE ("entity_id" = 'kitchen') AND "hvac_action_str"='heating' AND $timeFilter GROUP BY time($__interval) fill(null) into Flux to get the same results. Any help would be much appreciated. Thanks.