How to remove 1 reading in the energy dashboard

[Michael_Dahl]

Hi,

yesterday, my neo coolcam smartplug (z-wave) have 1 false reading, where it did send a kWh reading of 282.866,42 kWh, after this 1 bad reading it reported normal reading again. (that’s more then I use in 40+ years on the whole house)

but how do I delete that 1 bad reading ? my dashboard will reflect this bad reading when I use the month/year view ???

[tom_l]

How have you installed home assistant and what are you using for the recorder integration?

Using the MyPHPAdmin addon I was able to delete readings from the statistics table in MariaDB - I had help with the SQL query though so unless you want to scroll through 1000s of reading you may need help with that.

[Michael_Dahl]

MariaDB like you.

Do you you have the SQL query still ? i’m no sql expert

Well never mind, found the record, the MAX value it the only one with that many digits Did you change the values, or delete the record ?

[tom_l]

Delete.

I also changed some unit_of _measurement that were messing things up.

[Michael_Dahl]

Thanks for the help

[Michael_Dahl]

I spoke to soon…

The bad reading is gone in the DB, but still shows up in the energy dashboard, this cut is “last week”

[Protechtedd (Andrew)]

Michael, did you end up fixing this? I’m in a similar position and need to delete a record from showing in the energy dashboard.

[Michael_Dahl]

Yes, I found this…

2021.9.0: More energy, USB discovery, template

Not sure if you managed to get an anwser. The long term statistics are allso stored in the HA db if you use the standard config. You could install SQLite Web add on to explore this data. In statitics_meta you can find the ID for the sensor , then in statistics you could run: delete FROM “statistics” where metadata_id =‘YOUR ID FROM STATISTICS_META’ This will delete ALL data for the ID you select. Offcource you can add additional operators in the where clause to limit the results. (Allways …

[Protechtedd (Andrew)]

Great thanks. I ended up finding this post which is essentially the same as yours but with a few extra details. Linking it here for anyone who might find this and want extra details:

Energy Management - full data reset?

This process worked for me to ninja-delete a specific days worth of data due to a haywire sensor. Just a nutshell for anyone who might want to take an easier route and simply delete all the energy data of a specific day, this query will work: DELETE FROM "statistics" WHERE "created" like '2021-09-16%' Of course change the date itself, but leave the % symbol, as it is the wildcard (since the timestamp is always different). You can delete only a specific sensor instead of a whole day by more c…

[AbsolutJohan (Johan Lundgren)]

Hm, this doesn’t seem to work for me. I’m deleting a single day but the wrongfully accumulated data just moves one day ahead. If I delete the 2nd of sep it shifts over to the 3rd and so on. Wanna avoid deleting the whole database just because on day of measurement went haywire

[ingoratsdorf]

I’d say that’s because your sensor is of type “total_increasing”, meaning every reading is total, so you delete a day, the next day is still the total from the day before plus the daily use of that day.
You’d have to subtract the wrong reading from all subsequent reads.

[tom_l]

That was a two year old post.

Editing the database is no longer the way to do this.

Go to Developer Tools → Statistics and click on the ramp icon next to the entity.