In an earlier post, I made an error that incorrectly aggregated the energy data which resulted in hugely inflated aggregated energy usage. All the un-aggregated data was accurate, but the sums were wrong. Luckily I had all the raw data stored in InfluxDB and could rebuild it.
In this post, I walk through how to re-write the Home Assistant Long-term statistics database to fix this mistake.
A grossly high electric bill
First, I opened up the Influx UI and constructed a query to generate the corrected metrics. The HA database requires two fields: sum and state. One describes the cumulative running sum, and the other is the sum at the end of the hour. I didn’t fully understand how sum differed from state, so I just assumed they were the same.
I ran this query, then clicked download to CSV.
That gave me a file that looked like the following:
Delete first 3 lines and one of the two trailing lines.
Make sure the file is nearby on the host running HomeAssistant, then stop HA and open up the file:
I looked at my target table schema:
Then import the CSV file into the database
Now we’re reading to replace the data. First, find the id of the entity to replace. The first column will be the id
Then insert the data and drop the temp table
Then delete the history for the energy_cost because at the end of the next hour, HA will distort the metrics again:
Home Assistant stores some intermediary data in the statistics_short_term table. This may cause problems and I’ve had to delete those records sometimes
Then open home-assistant/.storage/core.restore_state and change the entry for main_panel_total_energy to match the latest value from InfluxDB.
Restart Home Assistant.
Voilà. The data is corrected: