Fix bad data in energy tab

Configuration
1

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

image
image1628Ă—486 15.2 KB

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

1 Like
2

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:

Screenshot_20211017-140026~2
Screenshot_20211017-140026~2995Ă—583 29.1 KB

3

I am using MariaDB yes. Possible to edit that?

1 Like
4

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

5

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

6

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

7

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

image
image1183Ă—752 177 KB

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

8

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

9

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

10

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

This is what you need

image
image1818Ă—1166 156 KB

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
11

Yup found them and updated

image
image1231Ă—297 59.1 KB

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

12

Also you need to edit the short-term statistics

SELECT *
FROM "statistics_short_term"
13

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

image
image1184Ă—280 61.3 KB

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

14

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
15

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:

image
image1136Ă—481 17.4 KB

1 Like
18

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

19

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
20

excellent! fixed all my broken statistics. Thanks!

22

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
23

Thanks, this helped me too