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.
Looking to do the same, at least get enough data to show up to the previous year. Did you find a way?
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).
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.
1 Like
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 
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:
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.
3 Likes
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.
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? 
1 Like
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.
2 Likes
Happy to help in anyway. This could be used for other things should as tracking a persons weight over time too.
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.
Yes you are absolutely right. But it is a major hurdle to run the purges per entity yourself 
Interested to see the results of such work! Good job guys!
Can you share the Grafana parameters, i’am struggeling with this a lot
You just select…
SELECT max("value") FROM "autogen"."W" WHERE ("entity_id" = 'power') AND $timeFilter GROUP BY time($__interval) fill(previous)
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.
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.
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?
The screenshot of mini-grap-card, I have posted, has daily usage not weekly.
I think it doesn’t make sense to display data for longer periods than 7 days (but TBH I haven’t even tried).
The problem here is that your mini-graph-card will need to fetch all the data for that period (every single value change) so it can be a lot of data. Mini-graph-card has some browser caching mechanism but if you login on the new browser / incognito-window / different device it will take some time to download everything from HA.
Downloading a lot of data is not the only problem. AFAIR (when I have done some changes in mini-graph-card) it caches the actual data - not the aggregates. This means on every page render it has to process all of the data. I guess it can kill/freeze your browser on slower devices.
The only reasonable option to show data for longer periods in HA is Grafana. It is processing and aggregating data server-side (in DB engine). But of course it has downsides as well (mentioned above).
In terms of the utility meter tariffs config - it doesn’t matter. On the end such configuration produces two separate sensors - each for every tariff. You can display them on separate cards or combine them in one as mini-graph-card is able to display multiple plots on single card.