Fix bad data in energy tab

No idea what happened but got some crazy value for solar gen today, 600kwh instead of the actual 10 that it generated


How can I clear this as it is throwing off all my values, monthly/yearly/etc

1 Like

Currently, the only way to modify recorded Statistics data is by editing the SQLite database (I am assuming you are using the default and not MariaDB or some other type).

There are tools available to edit the SQLite database but you need to be familiar with SQL commands to perform modifications.

For example, SQLite Web is available in the Add-on Store:

I am using MariaDB yes. Possible to edit that?

1 Like

Would phpMyAdmin installed to my HA instance allow me to do that?

Here I had the same issue. Is it with solar edge?

I had disabled MariaDB and made the edit with SQLite Web. It looks like the only way for now.

I hope they gonna fix this issue. For me SolarEdge had en outage and all data was missing for a period. this was causing the lifetime energy to be enumerated the next time the data was available again. This should be caught my some smart before is enumerated into the statistics.

I made an issue on GitHub https://github.com/home-assistant/core/issues/57900
if you can plus it

So check it out, I found the dodgy values from earlier today using the phpmyadmin integration and I think I can delete those rows but sort of afraid of messing stuff up


I did a take a backup so will probably just do it anyway :smiley:

If it’s only today it should be fine. I guess. you don’t will miss too much data

Data is gone but the graph is still showing over 600kwh :frowning:
Also sensor data is unchanged
image

What you edited was not the statistics. So I guess you can’t get into this info with mariadb.

This is what you need


in meta you can find the number you need and in statistics you can query

query will be something like this

SELECT *
FROM "statistics"
WHERE metadata_id = 43
AND sum > 500

Yup found them and updated


Still showing over 600 in the energy for today after getting rid of the above, so annoying

Also you need to edit the short-term statistics

SELECT *
FROM "statistics_short_term"

Ok updated these too, still seeing the same bar chart as above :sleepy:
but I also noticed the sum value which is increased by 600, I only changed the state where it was > 600 to 10.1


Does the sum need to be updated for every entry since then until now?
Change it to 1200 or whatever?

You need to change all incorrect data. That why you need to do it with queries like this:


UPDATE "statistics"
SET sum = sum - (18493.302 - 824.4379999999983)
WHERE metadata_id = 43 AND sum > 850

3 Likes

Yup works ok now thanks, I had just changed the state value and not the sum one too.
Once I changed the sum to the correct value in both tables boom :slight_smile:

1 Like

Hello,
could you give me a hint how to edit the faulty data?
Where are the values for “(18493.302…)” from?

You will need to update your state and sum value in both of these tables:
image
You would actually use this:
SET sum = sum - 824
Where 824 is the value you want to decrease by

1 Like

excellent! fixed all my broken statistics. Thanks!

Thanks for this, to be more explicit
38.097 was the the difference from wrong value and 231 is where it started

UPDATE `statistics`
SET sum = sum - 38.097
WHERE metadata_id = 99 AND sum > 231;

UPDATE `statistics`
SET sum = sum - 38.097
WHERE metadata_id = 100 AND sum > 231;
1 Like

Thanks, this helped me too