Historical Heating Data (daily,weekly,monthly)

jhavens12 · November 15, 2018, 11:49pm

Hopefully someone can help me out - I thought this would be simple but it’s proving to be a challenge.

I just want a daily breakdown of how long my nest hvac status is in “heating” mode - that’s it. I know I can use the history sensor to get “today” or “yesterday” or “last week” - but I want every day.

I’m using influxdb and grafana but cannot for the life of me figure out how to use them to calculate the amount of “on” time of the heater per day. I’m using a template sensor to translate from “heating” to 1 and “off” to 0 to graph the data, but that doesn’t give me a clear breakdown of daily, weekly or monthly usage for comparison.

Does anyone have an idea of how to do this? Hopefully with some details? Thanks

chairstacker · November 16, 2018, 12:33am

Have you looked at the History Statistics Sensor - or does it not offer exactly what you need?

freshcoast · November 16, 2018, 1:41am

I also have my hvac status logged to Influx. It sends a “ac_on” and “furnace_on” state changes to Influx using binary sensors.

In Grafana, I plot it two ways. One, I just plot the On/Off transitions in a line plot along with room temperatures. Gives me a rough idea of when the system was active.

This graph was from “yesterday”. The orange/red line is just configured to be a certain arbitrary height just so I can see it, it doesn’t relate to the temperatures at all. Decent visual represent but doesn’t give me a good sense of time.

The second graph is using the Discrete panel plugin. It has several options for the display.

I have the percentage and total run times turned on in the legend. This is a better visual representation than the line plot and also gives you some data. And since it’s Grafana I can easily choose the time window to anything, yesterday, today, last 7 days, month so far, etc.

However you’ll notice that in both plots, there is data missing before about 7:00 am, when the heat has turned on, and the discrete panel is only accounting for 17 hours. This is because Influx does not work well with discrete values that are updated infrequently. The HASS influx component will not send sensor data to Influx unless the state of the sensor changes (off to on), which makes sense. So because my HVAC was off from before 12am until 7am, the Influx query doesn’t return any data points in that window, and so Grafana doesn’t know how to fill it in. Influx itself does not fill in this missing value, when it could (take the last value before the query window) but there is a long open feature request to implement it (from 2016). There is also a feature request in the discrete panel to work around this (probably a problem for other DBs as well), but it’s still open.

I thought the HASS history component will fill in the values outside of the requested time window, but I’m not 100% sure. If so, and the history statistics sensor also does this, it sounds like it might work well and maybe it’s all I need to show the most useful times, e.g. today, yesterday, this week, this month, etc.

freshcoast · November 16, 2018, 2:13am

Well, that was easy. I added a sensor to track the time my furnace has been on today.

Looks like the discrete panel rounds up to the nearest hour, so I think they are matching. It doesn’t give you the nice visual representation of the run time, but good enough for everyday use.

bradyn12 · November 16, 2018, 2:58am

I answered how I personally do this on this other thread:

Edit: just reread the original post and realize you want to see daily totals going back, not just a total. My solution isn’t the answer.

freshcoast · November 16, 2018, 7:38am

I probably also misread the original post. The linked thread explains why HASS is not great for storing and using long term sensor data. If the desire is to get totals for N days back, you’re really limited to the number of days the recorder component is configured to save data. My recorder is set for 21 days, which is probably more than most. I doubt it’s a good idea to store a whole year,for example.

The automation example is a neat trick and works well for a cumulative value. I think if you’re already using Influx you might as well use it for this since it’s very capable of handling this kind of data.

Here’s what I’m trying:

Create a history_stats sensor to log the furnace run time with a time range set to “today”.

This is doing the important work of calculating the total “on” time for the binary sensor as it goes on and off. At the end of the day, this sensor’s value will be the total run time for the day. The sensor is cumulative over the entire day, so its value is updated as the day goes along (you can see the times increase in the history page).

Setup the influx component to record the daily furnace sensor events

Whenever HASS updates the history sensor, the Influx component will log it as an event with the current value. The current value is the cumulative run time for the day so far. This means that the very last measurement of the day is the total daily run time. It seems to update every 30 seconds or so (0.01 hours?). When the sensor is off there is no logging (time is not changing).

Use Grafana (or queries) to present the data by day

I created a table panel which queries the furnace measurement for the LAST(“value”) and groups by 1d. So in Grafana if the time interval selected is 1 month, it shows me the last measurement of each day of the month, etc.

You could probably embed the Grafana table panel (with some set time interval) into an iframe in HASS. Or just view it in Grafana.

Of course, all of the above is fixed to daily values. If you also wanted to ask “how long did my furnace run this month/year/last 6 months”. Probably a way to use influx to sum the daily values over an interval…

jhavens12 · November 16, 2018, 1:15pm

Thanks for this response, I appreciate the effort. Basically what you’ve said is what I’ve found in my many hours of googling by this point - its easy to find a guide to connect Grafana/Influx/HASS but then there are like 0 guides on how to actually set up a well through out graph that interfaces with HASS well.

I did set up the discrete displays and found the issue that you’re talking about. I thought for sure Grafana would have a more elegant way of displaying this information based on all of the positive things I always hear about it.

jhavens12 · November 16, 2018, 1:18pm

I just saw your other responses.

Thanks for working out the history_stats sensor part - I think thats what I’ll ultimately end up doing. It’s just a bummer that Nest only holds the information for such a limited time and while this solution will work for now, it’s still not idea but I appreciate your effort! Ill get started on setting that up now

jhavens12 · November 16, 2018, 1:36pm

I’ve gotten this to work now, i actually had the daily history stats sensor created a few days ago as that’s how I was calculating my total time in HASS, it appears to be working to get daily totals and will show days related to the time span selected at the top of the screen.

I guess I would have to create another table which aggregates these values together to get a monthly total? I’m sure that’s possible but I’d have to look into it some more.

Also if I select local time in the Grafana settings (I’m -5 UTC) it will show the totals at 7PM, but still the correct daily totals and that will make the rest of my graphs line up. If I select UTC in grafana as the default, the tables show at 12AM but then my graphs are 5 hours off. Anyway to fix this? Thanks!

jhavens12 · November 16, 2018, 2:33pm

This helped me fix the time zone issue only appearing in tables:

freshcoast · November 16, 2018, 5:24pm

Thanks, I had the same problem. Adding TZ() to my query fixed it.

123 · November 16, 2018, 6:34pm

How about this?

Linwood · March 7, 2020, 5:10pm

This is old, but similar to a problem I have. I did not exactly solve it but I have what I think is a slightly different approach in case it helps anyone who ends up here. See:

https://community.home-assistant.io/t/handling-long-term-cumulative-historical-data-finalizing-days/176617/4

Loader23 · July 10, 2020, 1:06pm

@freshcoast
Can you show me your query? I have the same problem.
With this query I only get the last value but not the ones before. The * 60 is there for the converting to minutes:

SELECT last("value")  * 60 FROM "h" WHERE ("entity_id" = 'heater_history') AND $timeFilter GROUP BY time(1d)

freshcoast · July 10, 2020, 4:18pm

Not sure this helps you or not, here’s what I’ve got.

SELECT last("value") FROM "hvac_time" WHERE $timeFilter GROUP BY time(1d), "entity_id" fill(0) tz('America/Los_Angeles')

Loader23 · July 10, 2020, 4:52pm

Sadly no, I just want the stat. There are two entrys in “value”, 0.660 and 0.630. Which is about 39 and 38 Minutes. I just want him to display 77 Minutes or 1 hour and 17 Minutes or something like that and all i got is 0,18…
I want to be able to use the date picker, select a time range like 7 days and he should show me sum of those 7 days. Any Ideas?

Loader23 · July 11, 2020, 2:24pm

I found a solution:

SELECT SUM("max") FROM (
   SELECT MAX("value") 
     FROM "h" 
     WHERE $timeFilter
     GROUP BY time(1d)
)

I had a Problem with the recorder in HA, the purging did not reset the sensor everyday so I created an automation:

- id: 'DB Purge'
  alias: DB Purge
  initial_state: True
  trigger:
  - platform: time
    at: '00:00:00'
  action:
  - service: recorder.purge
    data:
      keep_days: 0
      repack: true
  - delay: 00:00:10
  - service: homeassistant.restart

This will purge everything at night, so Influx really have just the values for one day.