Best way is to fix this is “manually” with sqlite3. The database file we’re looking for is home-assistant_v2.db. Have a backup first of that file before continuing.
First you have to “limit” your queries to the sensors you want to delete the wrong values (in my case i deleted the erroneous values from the tmeperature sensors), so you need to find out the ids of the sensors you want to in the ‘statistics_meta table’ table, in the ‘metadata_id’ column, so:
in sqlite3, we can lookup for a name in that column like that:
select * from statistics_meta where metadata_id like “%{name_to_look_for}%”;
an example for my sensors having “temperature” in its name would be like this:
select * from statistics_meta where metadata_id like “%temperature%”;
that will give me a list with ids of those sensors. note them (metadata_id column). Next i’ll search for wrong values in the statistics table, using those noted ids like that (imagining that my sensor’s metadata_id would be 6,10 and 20):
select * from statistics where metadata_id in(6,10,20) and min=0;
(above, i’m searching for values with 0 in ‘min’ column - i know they’re wrong… and tipically when the battery of those sensors fail, they will output 0 as min and max) - adjust that value for what youre looking for.
also, do the same search for max=0:
select * from statistics where metadata_id in(6,10,20) and min=0;
Check both queries and look for what you want… if everything is ok… then we will delete those values in ‘statistics’:
delete from from statistics where metadata_id in(6,10,20) and min=0;
delete from from statistics where metadata_id in(6,10,20) and max=0;
now do the same thing in the tables ‘statistics_short_term’:
delete from from statistics_short_term where metadata_id in(6,10,20) and min=0;
delete from from statistics_short_term where metadata_id in(6,10,20) and max=0;
Now those values with min=0 or max=0 were deleted. if you look at your graphs, now you can see there are no spikes anymore. you can also adjust the parameters min and max to adapt to your case. in my case, those 0 values in ‘min’ and ‘max’ represent errors from the readings, and therefore, deleting them will give me correct stats.
hope that could help someone. cheers.
