Wrong start values after importing historical energy data

I have imported historical grid- and solar-data into the statistics table of homeassistant_v2.db
To prevent a big value changes in the sum column the imported sum data has negative values.

This gives me a problem with the start of the data (big negative usages)

I have tried to increment all the data for the sum values for the specific metadata_id. First this looks fine but after a while the new data is inserted and that has no increments and this will give a huge negative value.
It looks like the initial value of the state is kept some were else. Does anyone know how to fix this?.

I have no clue what you imported/where but I have been fighting with this a bit too. You have to update both statistics as statistics_short_term,
What I did is to first remove all short term from that sensor , then make sure with statistics it looked fine and then made 1 entry in shortterm to match the current value…from there on it (still) works

1 Like

Thanks for your reaction vingerha.

De conversion was done by creating a sql insert script and executing it with the SQLite plugin.
I did not clean the statistics_short_term. I will to try this solution and get back with the results.

I have tried your (@vingerha) solution.
It works.
To change the values with the offset I executed the following sql commands.

update statistics set sum = sum + <offset> where metadata_id = <statistics id>
update statistics_short_term set sum = sum + <offset> where metadata_id = <statistics id>

Thanks @vingerha

Great… then please mark my post for solution as it may help others :slight_smile:

I am also trying to store historical data from my MT681 meter

But I am at a more primitive level than you guys so help is greatly appreciated…

I have an optical head reader and I am using the standard script of Tasmota.

I know the command to read the historical data (day, month year up to 2 years )…
How do I read?
How do I read it once? Is there any way to tell MQTT to send once and then stop?

Or is it better to import from excel? How? (I have all values recorded manually )

Thank you!

Hi Rob,

I’m also facing this.
See: My post in the Energy category

What value are you referring to as offset in your sample query?