How to fix statistics data (e.g. energy data)

Funnily during several “events” (accidents / incidents) I had to correct data in the database many times meanwhile - and I’ve ended up using almost the same steps.

But this topic is a good description of the necessary tasks.

I just want to add:

  1. Meanwhile DB schema has changed. Sometimes it might be necessary to take a look at the “new” table state_attributes too (usually not for fixing statistics data but likely for other purposes).
  2. To fix things 100 % I always had to edit (in that order):
  • states table
  • statistics_short_term table
  • statistics table
  1. Take causion when using date filters: the timestamps in the database are UTC, this might/will vary to your actual time (+/- X hours depending on your timezone).
  2. I learned to usually never delete rows from any table, as this often leads to FK constraint violation which immediately renders the database inconsistent ending in an automatically created new one with zero content. So while DELETE is very dangeorous to use, UPDATE statements with still knowing exactly what you’re doing seem to be more safe.
  3. For deleting (a lot of) orphaned statistics here’s a great how-to guide:


Also have run into this problem with suddenly incorrect data in database, now its solved after hunting wrong entry in Developer Tools → STATISTIC. But why this happens and how can we avoid it?


Why are not all entities available in Developer Tools → STATISTIC? For example, I have an entity with outdoor temperature, there are some errors and I can’t edit it :frowning:

Does anybody know how to export hourly kWh usage? I extracted the sqlite tables but can’t get it to add up the way as the energy dashboard.

For example
2022-12-01 00:00,0.29
2022-12-01 01:00,0.66