Energy Card: How to delete/purge records from an "early" misconfiguration period

[gjesch (Georges)]

I am a happy user of Energy Cards - Home Assistant

I first played around with the card end of December 2021. But some sensors were not yet working as expected (energy consumption via node-red-contrib-smartmeter). I fixed all sensors needed for the dashboard by April 2022.

Is there a possibility to purge records with the nonsense data from the database for a period like 2021-12 to 2022-04. Are the values transparently accessible in a SQL database? I know SQL but I have now idea how and where HA is storing its data, which keys or identifiers are used for the Energy Card etc.

I am happy to follow standard instructions if there are any.

Thanks for all the good work done by HA contributors.

[vingerha]

@gjesch

values transparently accessible in a SQL database

Yes if you know SQL
statistics_meta gives you the metadata_id for the entity
statistics the … dûh…statistics per metadata_id
I suggest to first change the metadata_id to something else, see what it does and only then delete…and make a backup

[gjesch (Georges)]

Understanding the Energy Card

I started exploring the data with SQLite Web.

• Is there an “overall” explanation around, how the recorder works and how statistics are calculated (and recorded)? I couldn’t find such obvious information myself.
• Is the flow like this? states (like snapshot values, real “recording”) → statistics_short_term (5 min) → statistics (1 h)
• Because in the statistics_short_term
• What does the Energy Card pick up of these? I couldn’t see a metadata_id that looks like originating from the dashboard directly. Has the Energy Card any influence on the mechanism states → statistics_short_term?
• If I would be able to find the exact “wrong” values in states and replace or delete them, would statistics_short_term and statistics be recalculated?

DB and Recorder - Home Assistant stuff:

As I started looking closer to this DB stuff and reading some posts like this Energy dashboard not showing historical data - #5 by wouter_klaassen, etc.

• I am afraid to continue with SQLite, I reached already 3.4GB
• Cascade delete is nice, but doesn’t work in the SQLite web client directly: Statistics - Fix Issue - large amount of data
• So I consider switching to MariaDB, I read already this page Recorder - Home Assistant, but what can be recommended?
  • I run HA in a VM on my Synology
  • I could run MariaDB natively on the Synology and use an external SSD for the storage => stressing my spinning HD less
  • Migrating home assistant database from sqlite to mariadb - #34 by fversteegen

Should I start a new thread for the DB migration part (MariaDB outside HA)?

[vingerha]

@gjesch

Is there an “overall” explanation around, how the recorder works and how statistics are calculated (and recorded)? I couldn’t find such obvious information myself.

I have not found one, others seem to not have this too, which does not mean it does not exist

@gjesch

Is the flow like this? states (like snapshot values, real “recording”) → statistics_short_term (5 min) → statistics (1 h)

That is what I see, I am however not sure why/when short_term are ‘reset’ as this provided some odd issues with me (and others)

@gjesch

If I would be able to find the exact “wrong” values in states and replace or delete them, would statistics_short_term and statistics be recalculated?

Never tried it but I guess so and would expect this only for the future stats, you can always try this out …and let me/us know

@gjesch

Should I start a new thread for the DB migration part (MariaDB outside HA)?

Whether or not you change…I have not seen great performance differences but this probably depends on how many updates you have. I myself did (!) migrate to MariaDB because of similar reasons as you mentioned. There are other posts on which MariaDB to use. I am on docker so using a separate container…but in fact also the add-ons are containers so should not matter much. You can always try one setup and then mover to another when you seem to feel a need to. My view: start simple…