How to delete old Energy Data

Configuration
1

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

1 Like
2

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:

2 Likes
3

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

4

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.

8 Likes
5

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…

10 Likes
6

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 :wink:

7

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 ?

8

Maybe this?

9

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.

10

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

11

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

12

I have exactly the same question @patric.dahl

13

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

1 Like
14

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

15

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

16

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.

1 Like
17

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 … ?

1 Like
18

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

19

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

20

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!