I use a sensor running Tasmota that reads data from my utility meter to graph the energy consumption in Home Assistant. This has been working mostly fine until I experimentally added a solar panel recently: It now has happened twice that the Energy dashboard in Home Assistant has shown an absurd value that's near or around the total consumption, instead of the difference to the previous measurement.
After finding this post on the issue tracker (cache), I'm moderately certain the underlying issue is that my sensor definition is using
After the first time this error showed up, I found a post that tells how to fix wrong values in the Home Assistant sqlite database (cache):
In my case, I only have one Tasmota sensor, so using the sqlite3 shell a
select * from statistics_meta where statistic_id like '%tasmota%';
gave me the id of the sensor (9).
Then I just ran
select id,state,sum from statistics where metadata_id = 9;
and paged back through the returned values to find any irregularities. Since the error had occurred recently, it was easy to visually identify the point where the sum value jumped up.
After calculating the offset to get the next increment from the correct sum, I updated the sum data for all rows that had the wrong value:
update statistics set sum = sum - 8654.0847 where metadata_id == 9 and id >= 143546;
...and then I applied the same offset to the values in statistics_short_term, which were all wrong:
update statistics_short_term set sum = sum - 8654.0847 where metadata_id == 9;
This still leaves me with the same corruption in the cost data that's calculated from the energy usage, but since I don't use that information, I just ignored this detail.
After finding this post on the issue tracker (cache), I'm moderately certain the underlying issue is that my sensor definition is using
state_class: total_increasing, which gets reset if the measurement value is ever decreasing. Now in theory the utility meter counter shouldn't allowed to be decreasing, but it's not impossible it has the wrong configuration. I have now changed the setting to state_class: total (which will also make it possible to track if that's actually the problem I'm having).After the first time this error showed up, I found a post that tells how to fix wrong values in the Home Assistant sqlite database (cache):
- retrieve database ID of the sensor from the
statistics_metatable - look at data for that ID in the
statisticstable - update
statisticsandstatistics_short_termwith correct data
In my case, I only have one Tasmota sensor, so using the sqlite3 shell a
select * from statistics_meta where statistic_id like '%tasmota%';
gave me the id of the sensor (9).
Then I just ran
select id,state,sum from statistics where metadata_id = 9;
and paged back through the returned values to find any irregularities. Since the error had occurred recently, it was easy to visually identify the point where the sum value jumped up.
After calculating the offset to get the next increment from the correct sum, I updated the sum data for all rows that had the wrong value:
update statistics set sum = sum - 8654.0847 where metadata_id == 9 and id >= 143546;
...and then I applied the same offset to the values in statistics_short_term, which were all wrong:
update statistics_short_term set sum = sum - 8654.0847 where metadata_id == 9;
This still leaves me with the same corruption in the cost data that's calculated from the energy usage, but since I don't use that information, I just ignored this detail.