How to delete old Energy Data

[mstoffel-sag (Marco Stoffel)]

Hi Experts,

I totally love the new Energy functionality, but I have one issue: I think I made a wrong configuration and wrong values where captured during a time period. Hence the long time statistic is completely wrong. Is there a way to delete data before a certain date?

I did not find any speciall entities that I could reset.

Any input is greatly appreciated!

Thanks Marco

[123 (Taras)]

You have to delete them from Home Assistant’s database. That means using a tool that can read/write the database and, using an appropriate SQL command, delete the unwanted records. Home Assistant should be disabled during this procedure to prevent potential database corruption. For details, refer to this post:

Energy Management in Home Assistant

For those wanting to dig a little further into their Energy visualisation not working, you can use the “SQLite Web” addon to look under the covers at the statistics and how it works. It appears some entities from plugins will have all the appropriate attributes to appear in here, if yours aren’t, check the posts above that mention last_reset: attributes: last_reset: '1970-01-01T00:00:00+00:00' To get started, you will need to install the SQLite Web addon and open it…

[tom_l]

You have to edit the statistics table of your home assistant database. Or delete it (the whole database, not the table).

[mstoffel-sag (Marco Stoffel)]

Thank you very much for the fast reply! Manged to delete the whole day. I use MySQL and did:

DELETE FROM `statistics` where metadata_id='8' and created BETWEEN '2021-09-10 00:00:00' AND '2021-09-10 23:59:59';

DELETE FROM `statistics` where metadata_id='7' and created BETWEEN '2021-09-10 00:00:00' AND '2021-09-10 23:59:59';

That deleted the faulty entries for from and to grid.

[mstoffel-sag (Marco Stoffel)]

Turns out that is not enough. Deleting the db entries must be accompanied by:

Log into hassio.

ha core stop
vi /mnt/data/supervisor/homeassistant/.storage/core.restore_state
# Set the entity to 0.0
ha core start

then delete the database entries. Would be really nice if there was a service that could reset entity state in the dev tools…

[patrickse (Patrick Sernetz)]

I actually figured out how to correct the consumed kWh for the faulty day. But it actually still shows the faulty values for week,month and year. How can I correct this?

Any help is appreciated

[jm1981 (Jean-Michel)]

Same issue for me.
Faulty data from entity DB has been corrected (value reduced to normal value), but in the energy monitor, I can still see the wrong amount.

Is there a way to correct that ?

[tom_l]

Maybe this?

Recalculate statistics table sum to fix wrong energy data

I’ve been having some issues with the Energy data due to switching from Shelly integration to using MQTT. The sensors I configured over MQTT were sending the wrong data, causing wrong data in the statistics table for those days. My issue in particular was that the new sensors (which I gave the same name as the shelly sensors to keep the history) were sending data in watt-minutes instead of kWh, resulting in a very small ‘state’ number and increasing the sum value by a lot. This causes spikes in …

[jm1981 (Jean-Michel)]

Thanks!
It looks great.

From my side, before knowing that, I renamed my sensor before adding it to energy monitor.
The sensor was new, so it was not. A big deal to loose 1 day of data.

[patrickse (Patrick Sernetz)]

It was the same for me. It was actually a week of data I’ve lost. Not perfect but enough for now.

[patric.dahl]

Hi all,

High jacking this thread with a question that might be answered by someone who have been working with Energy DB.

I´m at the moment having a large DB and want to purge it. If a do would it also be that my Energy history will be impacted?

Thanks in advanced
Patric

[bramdedaele]

I have exactly the same question @patric.dahl

[greenbeast]

How does one clear this data when you can’t stop HA core (because it is hass os)?

[sed]

Same issue here. I have hass OS and when editing core_restore_state and restartting, my changes are not taken into account

[toastboy70 (Jon Wilson)]

I came to this thread because I had a similar problem: experimental energy meters from when I was setting my Home Asssistant up for the first time which are no longer needed. Maybe it was written after this thread started, but unless I’m missing something the Statistics developer tool has a button for altering historical data and for removing statistics with no associated state: https://[home-assistant]/developer-tools/statistics

[Liam-Whiteside]

This doesn’t seem to help due to the way the energy data aggregates values from the sensors then stores them elsewhere. eg I have values from August where the cost was 100x too large. The developer tools / statistics show me recent data, but don’t allow me to fix the historic energy data. As it doesn’t think there’s anything wrong, I can’t see any options to change data there either.
Ideally I’d want to recalculate the aggregate cost value for the month based on a new cost per kWh from a certain date on.

[jstammi]

The problem is, that the statistics table has a column “sum”, that contains the aggregated data at the time having created the row. So this includes the values having later deleted.

One has to modify all later rows, too.
Or there is a way to re-create the update/re-create the/all statistics … ?

[orangelizard (Jason Crosier)]

Following, I made the mistake of testing various sensors and would love to reset all the data.

[znac049 (Bob Green)]

Hi Marco, can you explain what you mean by “the entity”, please. When I edit that file, it has many entities in it with lots of data for each. Which entity and what is it I should change to “0.0”?
Thanks very much,
-Bob

[mstoffel-sag (Marco Stoffel)]

Uhh, long time, I think HA is repopulating the entity that you have mapped in Energy Config from that storage file, meaning you have to find that mapped entity in the file and reset it, but without HA running otherwise it will overwrite the file to save the current state. Afterwards you can cleanup the database. Then start HA again.

Hope that helps!