Hi,
I found out today something “incorrect” in my statistics but I think I know what happened. I’m sending this email as feedback to the HA team for them to take a look at this and maybe fix the calculation of the “sum” column in the database.
The history:
- I’ve a binary sensor installed in the gas counter. It counts every turn of the counter using a magnet.
- I’ve one automation counting on-off transitions of that counter and accumulating the values in a counter
- That counter is used as the source for the gas consumption in the energy tab.
Up till here this is all OK and working. What happened yesterday is that I found a mismatch between the value in the real counter and the value in my counter. It is not a “big” mismatch so I decided to go to the counter and just press “increase” button couple of times in the UI. But that caused a rare effect. This morning I saw negative values for the gas consumption so I decided to explore in the database. This is what I found:
As you can see, the ‘state’ column increases, but the sum column decreases in some rows compared to the previous row!
The source of the problem here was myself trying to update the counter and not producing the on-off transitions that are counted.
As this is IMHO a common mistake we all can fall into I think it is worth exploring and developing a “fix” in the statistics when this is detected
Hope this helps others as well.
Regards
Ignacio