Hi everyone. Can someone please let me know if the Home assistant’s energy monitoring data is stored in the Home Assistant V2 database file? I have an automation that purges the database every night and only keeps 1 day. I am going to be installing a Shelly EM to my panel soon and I had that question on my mind. I know that the Shelly has an internal memory but I am unaware it HA is using that storage or if its storing the history somewhere else? Thank you
The energy dashboard is actually using data from long-term statistics.
Statistics are stored in home-assistant_v2.db, but they are never purged.
1 Like
Thank you !!
Never purged? That’s a bit worrisome for those of us using the default database on an SD card.
Any way to purge them? Prevent them from being written in the first place?
Long-term statistics are different than the other objects in the database, which are stored exactly as they happened and are automatically purged after a period (default is 10 days). Statistics are never purged. Because they are summarized every hour, they only create 24 entries per day.
Statistics should not cause too much trouble considering the low number of entries they create.
Long-term statistics are kept for entities that have the right state_class.
I don’t think you can easily override the state_class of an entity created automatically by an integration.
OK, I checked by opening yesterday’s backup of the database. There are statistics tables which appear to have been created Dec. 10th, so five days’ worth of data:
- statistics_meta has 32 records. No problem
- statistics_runs has 1,490 records. That’s 298 per day.
- statistics_short_term has 29,800 records, or 5,960 per day, almost 250 per hour. But that table isn’t even mentioned on the Statistics document linked above.
I think that could just be a result of a database schema migration.
Long-term statistics were introduced in 2021.8.0, so unless you have been running an older version of Home Assistant until Dec. 10th, you’ll probably have much more data than just five days.
I looked at a database I backed up on Dec. 9th, when I was at Version 11.5. Those three tables were not in the database. As of right now I’m up to 31,340 records in statistics_short_term and 1,567 in statistics_run.
This is not looking good. I’m not using energy monitoring, and I’d hate to lose my SD card and have to re-install HA because of this. I’m surprised there’s no way to turn this off, or at least de-select entities which I have no interest in tracking right now.
1 Like
Look into using the recorder in yaml. You can add only the entities that you want to store history by only adding “include”
I already use lots of excludes. Is there a way to exclude the energy monitoring data?
Conversely, if I switch to only including things I want to keep (a good strategy), will that exclude the energy monitoring data?
I ask because it seems like energy monitoring data are treated differently (and are stored in different tables) than state changes and events. I’ve yet to see any documentation which offers a way to exclude them. I’d love to be proven wrong, if anyone has seen such a document.
Statistics aren’t just for the energy dashboard, you can also use them with the Statistics Graph Lovelace card.
I believe I read somewhere in the docs that statistics are calculated every hour based on recorder data. If an entity is excluded from the recorder, statistics cannot be calculated for it. (I definitely cannot access long-term statistics for entities that I have excluded from the recorder.) However, I cannot find that documentation article right now.
That’s weird. I checked my backup from 2021.11.5 and the tables were there.
According to the release notes, they were in the database at least since 2021.7:
Database (statistics table)
The statistics table is a Home Assistant data table that is not exposed or used by Home Assistant yet and is part of an alpha / feature that is in development. However, it does exist and you might already want to check it out or find a use for it.
In this release, the content of this table is reset. This does not impact any state history and this data isn’t used by Home Assistant as of yet.
If you have no idea what this message is about, you can safely ignore it. We have merely listed this to be complete in our breaking changes report.
2021.7: A new entity, trigger IDs and script debugging - Home Assistant
That’s good news, thank you! Of course, there may be entities I want to retain in recorder, but not in the energy monitoring tables. So I’d still like a way to exclude or otherwise prevent writing to these tables.
1 Like
I guess I’m a bit surprised that there is not more discussion around this. First thing I noticed when Energy was introduced into HA, is that it just appeared on my dashboard, in the left hand nav bar. I can remove it, along with the Media Player, neither of which I use currently, but since I use Private Browsing in FF, every time I restart FF, they’re back. I think it would be helpful if these settings were persistent. But I’ve given up hope of that for now and live with them taking up space.
I’m with you CaptTom, still using a DB on my SD card (until I can get an Amber!). I find it odd, given how HA is the ultimate in configurability, that energy monitoring is not configurable, and can’t be disabled. I guess it was assumed that everyone uses energy monitoring? I might in the future, but now it is just an extra icon and wasted disk space.
1 Like
Energy can be disabled. It’s part of the default config, but it is not required in any way.
This documentation may help: Default Config - Home Assistant
If I understand the default config correctly, you can’t have default config and then just exclude certain ones that are part of it. It’s either all or nothing, leaving you with the alternative to then enter all of the integrations individually in your config file (I have 74, some I have no idea what they do, and definitely wouldn’t have thought I needed them building a list myself).
default config is awesome, and I suspect that MOST people use it without even thinking, as I have for 2 years, but there are probably things running in my setup that I don’t use or don’t need, which could be removed, which might even speed things up. I’m not sure I could determine which those are to any level of accuracy.
It would be nice if there were options for the default config where you could add an exclude: block to eliminate those integrations you DON’T want as part of the default config. Unless that as been added, I don’t believe that is an option (like it is with history, recorder, and …), but one that would be worthy of consideration.
1 Like
Hmmm. I don’t use default_config. And I did try adding “energy:” to my configuration.yaml once, but have since commented it out. Should I just delete the associated tables from my database?
And what if I want to use energy monitoring in the future, but don’t want to keep the data forever? Do I have to manually purge the tables?
Energy monitoring seems like a pretty important feature. If I were starting with HA today, that’s probably the first thing I’d use. As it is, I have other ways of saving my energy usage data (outside the HA database.)
But unless I’m totally misunderstanding this (very possible), we’re setting up new users for failure, recommending Raspberry Pi, SQLLite and SD cards. It seems we either need much more robust hardware and software, or we need to split out long-term historical data from real-time dynamic data.
Edit: I just checked the DB, and it’s still storing thousands of records in those statistics_ tables. Almost 6,000 in statistics_short_term just in one day, yesterday. I don’t have energy: in my config. I don’t use the Lovelace statistics card.
You could try to override the state_class attribute in customization, to keep the history but disable statistics.
I have not tried this, but it would make sense.
The statistics are used for several different kinds of sensors, not just energy. So it’s not really just a matter of “I never opted in to energy monitoring”.
for the record, there is no statistics_long_term table in sqlite looks like. But there is a statistics and statistics_short_term. Maybe the table names changed over time. The statistics tables does have the “longer” term energy storage stats using the metadata_id from the statistics_meta table. I am using this to export data into influxdb. Could probably use a tool like Datagrip to do it more easily, but using the export with the Home Assistant Sqlite web add on, then bulk upload.
I recently moved my database to mariaDB on my synology nas. I want to purge the data like HA does very 10 days but I do not want to lose the energy data from my energy dashboard. Or does HA still purge it automatically when the DB is external. if Not I how can I only purge the other data and keep the energy data. Also data from my temperature sensors I dont want to purge.
is this the data that is used on the energy dashboard?