Hi all, relatively long standing user of HA (> 1yr) but haven’t had to post much because most everything I encountered someone else has already experienced… until today.
My setup includes reading my electrical usage via a SDR through MQTT. It is from what I can tell, a pretty vanilla setup (this particular setup, using scm).
It’s been running for a week or so quite smoothly in the new Energy card (prior to 2021-08.x I was just playing with the SDR and hardware and rolling the output into a csv and processing manually).
But this morning there was a spurious report so that my Energy card now shows 4.8 million kWh during a one-hour slot. Obviously this not only affects the daily view, but also the monthly total.
I would like to remove that result, and it should be easy to find, if I knew how. I assume I need to edit the home-assistant_v2.db using sqlite, but honestly have no idea how, nor even if this is the best approach.
I looked for other topics on how to remove data from the db file, but didn’t find anything that matched on removing single records, just purging. I’d prefer to keep my usage intact, excepting that single record.
EDIT: I’m running mqtt on a Raspi4 and HA on a Raspi3B+ if that helps (particularly with the editing portion).
Hoping the collective wisdom can help me sort this out.
Okay, I made progress, using squlite3 to find the erroneous record and to overwrite it. Saved the db file and verified it works.
BUT: in the Energy card, the spurious hourly usage persists! So where is the storage for the Energy card if it’s not in the home-assistant_v2.db file??
EDIT: Yes, I waited for the next hourly refresh at :12 past. No change
EDIT 2: I did restart the core as well, prior to waiting for the 12 after auto update.
I recommend doing this with HA stopped to avoid other issues. While I was typing this I lost my entire DB by having the DB browser open
You need to browse the home-assistant_v2.db file and look up the statistics_meta ID for the entity with the incorrect info. Once you have that ID, go to the statistics table and sort by that ID. Somewhere you’ll find where the SUM value abruptly changes. What I did was then copy the state values after the fault to an excel spreadsheet. From there you can build a quick column that finds the difference between each value, then another to add that difference to the last correct SUM value. Paste those values into the SUM column and write to the DB. Start HA and you should be good to go.
