Noob here, who has done a lot of research, and still struggling.
TLDR: i’m unable to wipe sensor history, because even after removing all related entries from ‘states’ and ‘statistics’ and ‘statistics_long_term’, a newly created Riemann helper always starts with an old value, instead of 0.
i have an MQTT sensor, which calculates energy used, but that is from an old device, and i’m unable to wipe that. so that i do, is that after pairing, i rename and disable the entity, and create a Riemann integral sensor instead, using the same power value reported by that sensor.
i have been able to do this for some devices, but currently i’m unable to figure out, why i can’t set my device to 0, it always reverts to the previous state. i looked up the metadataid, and removed all statistics and statistics_long_term entries, i also looked up the states and cleared all of them. i even allowed it to write false values again, just to rewrite those in the state history and stats, so it can use the latest, did not work. so far, other than renaming the sensor, i see no other option, but i consider that to be a very bad route, and want to solve the actual issue.
allow me to respond to myself:
entities are also stored in .storage/core.restore_state, which has the initial value for the entity, i guess for the case if the DB gets lost. after i set that to 0, my problem went away, and my sensor is now working as expected.
let me summarize for those, who wish to completely wipe a sensor:
you need to delete all rows in the states table, based on the entity_id
you need to find the metatada id in statistics_meta table, based on statistic_id, which is the regular entity id
you need to delete all values from statistics and statistics_long_term based on metadata_id you just found before
please keep in mind, that to remove only a range of time, it can also be done, but for “sum” sensors, you’ll need to update all fields by substracting the initial value, so you can start from zero, like so:
UPDATE `statistics` SET sum=sum-6.5299 WHERE `metadata_id` = 109;
UPDATE `statistics_short_term` SET sum=sum-6.5299 WHERE `metadata_id` = 109;
Great! This cleanup feature is really missing in home assistant. Especially when you wrongly set up power>energy>utility meteres, respectively when you do a single mistake, then you cannot repair it unless you choose a different name for new entities. That’s really something that should be polished on such an advanced system.
I am not sure whether you need to do the third step. I accidentally deleted the entries in the second step instead of writing down the metadata to delete statistics, moreover, I did not have statistics_long_term at all. Maybe because I use influxDB, so the related addon takes over? In the end, setting up the energy sensor from the power sensor and utility meters were quick and without problems. This is absolutely crucial post you did here. Thank you.