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

[e-raser]

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

3. 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).
4. 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.
5. For deleting (a lot of) orphaned statistics here’s a great how-to guide:

Statistics - Fix Issue - large amount of data

Abstract Introduction If you face a lot of statistic issues to be fixed (in my case about 7000), and you don’t want to bother by clicking thousands of time the Fix Issue button, then clicking the Remove button in the dialog message this little how-to might be useful for you. Unfortunately, the WebGUI doesn’t have a “Fix All” button, therefore we need to run the cleanup manually directly in the database. Overall cleanup process Disclaimer As we will directly interact with the backend datab…

[patric.dahl]

Hi,

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?

BR
Patric

[melwinek]

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

[Anteus]

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
Date,Usage
2022-12-01 00:00,0.29
2022-12-01 01:00,0.66

[bortek]

Due to a wrong cost of the energy price that I had I have manually deleted the cost data from statistics and statistics_short_term tables using the right metadata_id. Now the it started to populöate the tables with new data and correct cost. But I wonder if it is possible to re-generate the data for the past X number of days? How?

[heebo1974]

Anyone know why there are no created date in the data ?

select id, created, state, sum from statistics
where
metadata_id == 172

[e-raser]

This topic is about fixing statistics data, not about discussing database structure observations. I’m very sure you’ll find another topic where this question is more suitable or even has been answered.

[heebo1974]

Well I have to fix my database and I am not able to use these fixes because of that empty data.

[vinceg0267]

Hi, can you help me solve this?