I’ve seen a lot of posts saying that to delete energy data, I need to stop HA and delete rows from several the SQL tables. But how do you stop HA when using Home Assistant OS on a RPi? The posts I saw are also inconsistent when talking about which tables to delete rows from.
Is there a better way to delete the old data? I’d like to delete all of the energy data at once so that I don’t have to correct the entities that accumulate data, but I don’t know how.
Context: I just installed an IoTaWatt today but it was initially configured wrong. Now, somehow I have massive negative values for energy consumption that ruin my data.
I tried something but it didn’t work. I think I’m really stuck.
I deleted the IoTaWatt device and integration, as well as the CO2 Signal integration.
Then I used Developer Tools > Statistics, found each of the entities I’d removed, and clicked Fix Issue for each one which said it would clear all of the long term statistics data.
I restarted Home Assistant and readded the IoTaWall integration which repopulated my entities.
I was expecting all the aggregated entities to start again at 0Wh, but they kept their incorrect values. e.g. some are very very unrealistic negative values.
UPDATE: I just tried this a second time with an extra restart between deleting the integrations and fixing the statistics in developer tools. It still didn’t work, and I’ve noticed that even though my IoTaWatt entities no longer appear in the SQL statistics_meta table, there are still heaps of occurrences in the states table. This brings me back to the same problem where I can’t access SQL to delete data after I stop HA through SSH (ha core stop).
How can I delete data using MySQL over SSH after stopping HA?
I’m using the community SSH & Web Terminal Community Add-On.
Is there a default user and password for the home-assistant_v2.sql database?
This fixed the issue I was having with accumulated data not resetting. However, this didn’t fix the issue I’m having where the *_wh entities are reporting insanely high values. That may just be an unrelated issue that I have to look at separately.
Remove the relevant devices and entities.
Delete the IoTaWatt Integration
Delete the IoTWatt entities from the Energy dashboard.
SSH into the machine running Home Assistant OS and stop Home Assistant
ssh user@ip
ha core stop
Create a backup copy of home-assistant_v2.db just in case we corrupt the database file.
-- Use these to find the data to delete.
-- Change the search text to match your entities.
SELECT entity_id FROM states WHERE entity_id LIKE "sensor.main_line%";
SELECT statistic_id FROM statistics_meta WHERE statistic_id LIKE "sensor.main_line%";
-- Use these to delete the data.
-- Change the search text to match your entities.
DELETE FROM states WHERE entity_id LIKE "sensor.main_line_wh%";
DELETE FROM statistics_meta WHERE statistic_id LIKE "sensor.main_line_wh%";
Exit the database
.quit
Do this and set all the restore states for the accumulated entities to 0.0. However, don’t do this while HA is running because it will keep restoring this file, ignoring your changes. Edit from SSH.
#vi, nano, or whatever you prefer.
vi /root/config/.storage/core.restore_state
#Then find any of the relevant entities and set their state parameter to 0.0
[/quote]
8. Use this command to check if the states were saved correctly.
cat /root/config/.storage/core.restore_state | grep accumulated -A 1
Start Home Assistant (or just reboot)
ha core start
Check the core.restore_states file again after starting HA to make sure that it wasn’t rebuilt without your changes.
cat /root/config/.storage/core.restore_state | grep accumulated -A 1
hi
after an update I lost all my previous energy data. I have older backup. Do you know if I can only replace the home assitant-V2.db file will be ok and the energy data will be restored? Is there an alternative to restore my energy data?
I’m not sure about that one. I think replacing home-assistant_v2.db could do it, but I know there is data stored in other locations somewhere. You may need to create a new post so that you can get a better answer. Sorry, and I hope that helps.