Correct or Delete Energy Data on Home Assistant OS

Energy
, ,
#1

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.

#2

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).

Delete HA Statistics (data)
Delete HA Statistics (data)1916×811 54.3 KB

Wrong Values
Wrong Values1524×859 71.2 KB

#3

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?

1 Like
#4

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.

  1. Remove the relevant devices and entities.
Delete the IoTaWatt Integration
Delete the IoTWatt entities from the Energy dashboard.
  1. SSH into the machine running Home Assistant OS and stop Home Assistant
ssh [email protected]
ha core stop
  1. Create a backup copy of home-assistant_v2.db just in case we corrupt the database file.
cp /root/config/home-assistant_v2.db /root/config/home-assistant_v2.db.backup
  1. Open the database file in sqlite3
sqlite3 /root/config/home-assistant_v2.db
  1. Delete the old data
-- 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%";
  1. Exit the database
.quit
  1. 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
  1. Start Home Assistant (or just reboot)
ha core start
  1. 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
  1. Add the IoTaWatt device back to Home Assistant.
Settings > Devices & Services > Add Integration > IoTaWatt
  1. Add the IoTaWatt entities back to the Energy Dashboard.
Settings > Dashboards > Energy
#5

An example of how to fix incorrect data recorded before a determined date, as a function of itself.

  1. Use something like this to find the data. Adjust the date at the end until you find what you want.
SELECT entity_id, state, last_updated FROM states WHERE (entity_id LIKE "%cost%") AND (last_updated <= datetime("2023-01-09 14:55:00.000"));
  1. Use something like this to edit it.
UPDATE states SET state = state / 100 WHERE last_updated < '2023-01-09 14:50:50.300' AND entity_id LIKE '%cost%';

An example of how to change incorrect data row by row.
e.g. I wanted to change state = unknown for the energy cost entity.

  1. Use something like this to find the row by changing the date at the end.
SELECT entity_id, state, last_updated FROM states WHERE (entity_id LIKE "%cost%") AND (last_updated <= datetime("2023-01-09 14:49:59.300"));
  1. Use something like this to verify that you have the correct row.
SELECT entity_id, state_id, attributes_id, state, last_updated FROM states WHERE (entity_id LIKE "%cost%") AND (state_id = 157235);
  1. Use something like this to change the data
UPDATE states SET state=0.0 WHERE (entity_id LIKE "%cost%") AND (state_id = 157235);
#6

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?

#7

Hi Makis,

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.

1 Like