[Solved] Energy readings negative after migration from SQLite to PostgreSQL

Hello all,

I recently tried to migrate from SQLite to PostgreSQL database for Home-assistant. For me, it’s most important to be able to transfer energy statistics (electricity and gas usage). I first tried to migrate with pgloader but I had no success due to some errors in the authentication. Then I simply exported Statistics and StatistiscsShortTerm tables to csv format from the SQLite database and imported them back to PostgreSQL. That was a success! I can view all old energy statistics in HA! however when transitioning from imported data to new data I have huge negative consumption…

Why is this the case and how it can be corrected? No entries in the database have negative values.

1 Like

Found the issue! the tables Statistics and StatistiscsShortTerm have a column Sum, which is essentially a historic sensor value minus the value home assistant first recorded for that sensor. But if you import old data this Sum column at the transition between old and new will go from a large value to 0. Because of this HA thinks that energy consumption was negative.

I exported the data to csv file again and recalculated the Sum Column. Now the energy consumption is correct.

1 Like