Utility meter reports (daily/monthly/yearly)

maxwroc · May 22, 2020, 1:58pm

Long long time ago I was using Domoticz where you could easily see the usage reports from utility meters (for current day/week/month/year). Additionally you were able to compare the year over year usage. Due to lack of such functionality on HA I was hesitating from migrating from Domoticz to HA for quite a while.

Example report from Domoticz (taken from the web - not mine):

Currently I was able to achieve weekly usage with utility meter and custom mini-graph-card (which I have contributed to by adding daily aggregation)

I was/am considering couple of solutions:

Extending purge settings for history/recorder (currently I have set it for 14d) and just use current (above) solution to display last month
Create a new card or extend mini-graph-card to call InfluxDB to fetch the data
Create automation which would store the value in some DB/file. Then read the content via file platform sensor and later parse the value via some custom lovelace card (e.g. mini-graph-card)
Create a custom integration which would be aggregating values and exposing them via single sensor

First option is not a good idea as it will just increase a lot the DB size (with really redundant data - as you cannot set retention policy per entity and I don’t want to store all sensor readings through the day for each day in the month). And it would not solve the problem of monthly aggregation (for the year).

Second option I’m not sure if it’s feasible as I don’t know if I won’t have problems accessing InfluxDB via ajax. And anyway AFAIK there is no way currently to do monthly/yearly aggregation as it’s query lang doesn’t support it. I have read that there is a way to tag the entries with some plugin for Telegraph. This way you can mark each item with day / month name / year. But this would require installing additional custom integration (telegraph) then the plugin in the telegraph for tagging… So it looks like a long and complex journey which can be blocked at some point by a random problem (so it may turn out to be just a waste of time).

Third one looks reasonable and feasible but there is a lot of custom configuration and I was wondering if there is maybe some easier solution.

The other option which I consider is to write custom integration which would do the aggregation, store the calculated values in HA DB and expose the final results as a sensor. The problem is that I’m not a Python dev and again this can take some time. It would require as well a custom card to display such data I assume.

So that is why I’m here asking if someone had similar problems and solve them somehow.

The other thing would be nice to know is how many people would be interested in such functionality (hit “like” below this post if you are).

Feature request: Why The Heck: Does Domoticz have a better utility meter report then HA?

Damage · July 20, 2020, 9:08am

I found this post while looking for solutions to the same problem. I wired my home with Bticino smart plugs and switches plus a “main” power meter, and the bticino app has a nice “metering” section where you can see bar graphs and pie charts with consumption breakdowns. Now I’d like to remove the Bticino gateway and connect the devices directly to HA via zigbee2mqtt, but I’d like to copy the visualizations from the Bticino app, and also improve them a bit.

totalitarian · August 28, 2020, 6:22pm

Looking to do the same, at least get enough data to show up to the previous year. Did you find a way?

maxwroc · August 29, 2020, 10:01am

No, not yet (but TBH I wasn’t actively looking meanwhile). I was hoping to start here more active discussion as I thought there will be more people interested in having the same. Apparently that is not the case or discoverability of this thread sucks.

I think that the majority of people who started with HA as their first interface for automation just don’t see such a need. And probably they are more or less happy with Grafana.

Eventually I think I will try to work on some custom solution (and share it via HACS).

Cougar · August 29, 2020, 4:47pm

You can actually query InfluxDB for daily/weekly/monthly/yearly graphs as long you have enough data in InfluxDB. It is possible to query from raw data but it is better to make a continuous query with data retention to calculate these numbers. Basically, you just take maximum value over time from HA utility sensor.

I don’t have enough data collected but I can already see daily usage like that using my Home Assistant Grafana dashboard:

Query for that is

SELECT max("value") FROM "L" WHERE ("entity_id" =~ /^(water_hot_daily)$/) AND time >= now() - 60d GROUP BY time(1d), "entity_id" fill(null)

Similar query can be also used to take whatever time interval maximums.

maxwroc · August 29, 2020, 7:48pm

You cannot write similar query for full months/years (as the number of days in them varies).

And even here for days you get crappy outcome because the bars and their values, for the previous days, will be changing depending on the current time of the day. It doesn’t look at the full day (which starts and ends at 00:00) but 24h buckets (starting from the current time).
There is a solution for this though. You can specify special time range: https://grafana.com/docs/grafana/latest/dashboards/time-range-controls/?fbclid=IwAR3Wxx2P69rnLflHG9Ocwj0x6Mv5musUNeRrvO4TZgFiKsvmMDYuhzZPbVM#time-range-controls
It kind of works but since the time-range control affects all the graphs in single dashboard it is not a perfect solution. And of course it doesn’t solve the problem of months/years.

Some time ago I was really hoping it is possible to achieve via InfluxDB but then I have discovered that there is a feature request for exactly what I was looking for (for InfluxDB). Someone even tried to implement it (AFAIR) but it stuck at some point.

As I have mentioned, there is another way to achieve it - via Telegraph and some plugin. But I haven’t tried - these would be another two “redundant” components in my system to just achieve such simple reports so I don’t want to go this way

maxwroc · August 29, 2020, 7:52pm

BTW my post was linked in the feature request for HA (thanks @MisterGlass) so if you would like to have native support for such reporting please vote:

dgomes · August 29, 2020, 8:26pm

I’m the author of the utility meter integration. I log my meters into influx and use grafana to get my history to way back.

I support the idea of having every thing directly on HA, but I’m not a frontend developer and I’ve not been able find a way to push such long history into a graph.

Basically where we have the “last period” attribute, we could have an array of all previous periods (ok… there must be a limit somehow). This is analogous to the weather card which displays a week, but here we would need a special card for the utility meter.

maxwroc · August 29, 2020, 9:44pm

Thank you Diogo for chiming in.

Yes you are right that the new card / frontend work would be required. We cannot send all that data through the state so it has to be done via attributes. At least this is what I was planning when I was thinking about custom sensor component for doing that work. I have some experience in crating lovelace cards so I hope I will be able to help on that part.

Since I haven’t created any custom components can you tell me if you are able to store custom data in the HA DB when you are a sensor component? Because as far as I know you are able to read states of other sensors, you can produce your state based on them and you can keep some temporary variables in the memory but it wasn’t clear for me if you have the access to the DB.

In general I see this feature as an another “tab”/menu-item among Logbook/Calendar etc. You could specify in the config for which entities you want to get reports and the data for them will be gathered for you and displayed in the separate tab/menu-item.

Cougar · August 30, 2020, 8:34am

InfluxDB (+ Grafana) is hack indeed but AFAIK the only way to do it right now. I don’t worry about different month lengths actually. As long as one month total is not less than couple of days of next month, the result is correct even with a slight shift. Correct x-axis legend is another issue.

For me it looks like the usage of attributes is the best way to keep long history inside HA, at least as long as Recorder treats all sensors the same way.

Some users already do similar things “manually” in configuration using Automation and Template sensors.

Utility Meter already keeps one historic point in last_period. It only needs a configuration parameter like keep_history to specify the amount of periods to keep and last_points or similar attribute for a list of measurements. Should be quite straightforward to implement in integration. And then someone with UI skills should make a Lovelace card for that.

But in general, I’d personally like to see similar functionality for every sensor: possibility to keep specified number of historic values instead of time based limit like it is in Recorder right now. So, maybe it should be a Recorder feature instead?

dgomes · August 30, 2020, 3:51pm

You can already do something like that. You can disable Recorder automatic periodic purge, and implement your own purge in SQL.

I can easily come up with a PR to support a configurable amount of last_periods if someone with UI skills can fill the blank of providing a proper UI.

totalitarian · August 30, 2020, 6:14pm

Happy to help in anyway. This could be used for other things should as tracking a persons weight over time too.

Cougar · August 30, 2020, 8:08pm

I was thinking a little bit about graphs and isn’t it so that even existing graphs work out of box if there is enough data in database?

I haven’t checked it yet but I think that sensor that gets updated only once a day/week/month (via automation using last_period from utility for example) and not purged from database, should already show long term graphs.

Of course, then it is needed to run some external script or database procedure to do regular database cleanup instead of built-in purge.

dgomes · August 30, 2020, 9:50pm

Yes you are absolutely right. But it is a major hurdle to run the purges per entity yourself

mgim · October 5, 2020, 10:17pm

Interested to see the results of such work! Good job guys!

joostvanmourik · February 1, 2021, 1:37pm

Can you share the Grafana parameters, i’am struggeling with this a lot

dgomes · February 1, 2021, 11:27pm

You just select…

SELECT max("value") FROM "autogen"."W" WHERE ("entity_id" = 'power') AND $timeFilter GROUP BY time($__interval) fill(previous)

Djal · March 2, 2021, 10:26am

Hi @maxwroc in your first screenshot, how could you achieve the weekly usage with the mini graph card?

I only manage to achieve a daily usage. Only have the “Date” value available for the “group_by” property.

maxwroc · March 6, 2021, 1:02pm

First of all you need to gave the utility meter defined in your config.

utility_meter:
  energy_daily:
    source: sensor.energy_total
    cycle: daily

Then just follow the example from documentation of mini-grap-card.

Djal · March 8, 2021, 9:22pm

Thxs for answering.

already have :

  weekly_energy:
    source: sensor.qubino_goap_zmnhtdx_smart_meter_s4_s5_s6_energy_3
    cycle: weekly
    tariffs:
      - peak
      - offpeak

does the tariffs atrribute make a différence?