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

[Hamudulu]

SERVICE UTILITY_METER.CALIBRATE
How can this work? Could you show an example please?

[tayfun_kucuk]

@maxwroc Thank you so much for your post; it helped me a lot!

[osulivanDE]

Is ther maybe a simplier solution to fix those spikes?

[bassguitarist]

Dear all.
I’m facing a similar situation with wrong values in energy dashboard.
I had some problems due to current drops, resulting in a corrupted DB. I fix some way, and everything looks good now, except the very first data on Dec. 2nd 2021, where there is a spike I’m not able to detect within the DB.

Could you please help me figuring out how to solve my problem?
Thanks in advance

Simone

[bassguitarist]

Sorry for having disturbed you.
Looks like I had some balance data from a previous DB: ordering them by “start” column did the trick and I found the actual problem.

Sorry !

[pawelma]

I’ve written a query which will work if you have MariaDB backend. I’ve set a cron to execute it daily through mysql cli client (because I was lazy to create a proper script with an automation).

Remember to replace solaredge to your sensor (entity_id) if you’re changing to different provider.

update statistics
  join (
    select * from (
      select bug.id, fix.state, fix.sum, 
        row_number()  over (partition by bug.id order by fix.start desc) as rn 
      from statistics as fix
        join (
          select b.id, b.state, b.sum, b.start, b.metadata_id from statistics as b
          where b.state=0
        ) as bug
        on fix.start < bug.start and fix.metadata_id = bug.metadata_id
      where 
        fix.state != 0 
        and fix.metadata_id = (
          select id from statistics_meta 
          where statistic_id = 'sensor.solaredge_lifetime_energy'
        )
    ) as fix_rows 
    where fix_rows.rn = 1
  ) as fixes 
  on statistics.id = fixes.id
set 
  statistics.state = fixes.state,
  statistics.sum = fixes.sum
;

It copies last valid row to the one which data has been missed

[macxq]

This can be also fixed using build in HA mechanism How to fix Energy Dashboard data (statistic data) · macxq/foxess-ha Wiki · GitHub . Of course when amount of discrepancies is low

[azerbinati]

Hello all, since goggling around for this issue show a lot of results about SQL query, I want to share my experience hoping to make some people happy and avoid messing data around, because you can fix this very easy.

I had a spike of 200~kWh logged in this morning, don’t know why, but I was able to fix this directly from the interface of HA without any SQL messing.

This was my ugly/demoralizing graph this morning…

looking at SQL this was the raw data (not needed to check at all… but gone there following google hints)

As you can see, the meter jumped from 18k to 226k, so not an HA issue.
Just open Developer Tools, go statistics and click on the “ramp” icon to the right

navigate to the point the graph gone broken and enter a new value (0 or estimated)

Enter the new value (0,09kWh was the right number for me)

And It’s all done

As you can see, graphs returned to normal.
(BTW: I had to fix cost statistics too, same procedure).

Hope this helps

[maxwroc (Maks)]

@azerbinati This is exactly the same way which @macxq shared a post above you. But it is good to have the details here as well - just in case the link stops working in the future.

It is true - at some point HA devs added a way to fix the stats easily (TBH I don’t know when they have done it, I haven’t noticed any mentions about it in any release notes).

Although for me the initial stats are still broken (very first record)

The first recode available via “Adjust sum” UI is the following

It looks like it uses the sum (instead of state) value to show the first record

[napalmz]

I tried to fix the statistics table by first editing the “state” field and then the “sum” field. The data seems correct right after the change but then within 1h the same difference that I entered in the last hour data reappears.
Is the state saved somewhere? Is there any variable I need to reset after reclaiming the data in SQLite?
I specify that the data I need to fix is not the main energy tab but the individual utilities immediately below it (which are a UPS and a 3d printer).
Thanks

[Mikkel-ES (Mikkel Einvik Stryker)]

I had the same problem. And the solution is that you need to update the “statistics_short_term” as well. I belive the “statistics”, both “state” and “sum” are calculated from the last hours of “statistics_short_term”. As this is a mutch more detalied datasett.

[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 caution 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:

https://community.home-assistant.io/t/statistics-fix-issue-large-amount-of-data/419728

[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?