Too bad there isn’t an easier way to do this without DBA skills.
Feature request?
I wanted to add my comment here how I managed to fix some missing data and reset counting.
I am using SQLite database, so I searched for an editor first which has visual options to edit. As in my case, there were multiple issues and required some manual editing and I couldn’t just do an SQL query to fix it.
First of all, I made a backup copy of the database!
Then I tried RazorSQL first, which has a 30 days trial, but it has turned out that my DB, about 7 GB size, is too big for it and it cannot open it. Bummer…
So I did a search again and found SQLite DB Browser which has visual editor functions as well.
It was able to open my big database as well, but if you want make things smooth, make sure you would copy your DB file on your PC to an SSD drive and not a HDD, plus have enough free memory.
On the bigger tables it was still slow, but you can filter by values easily.
All the editing I did in Excel after I copied the data from the table in the Browse Data tab. Make sure that decimal point is set to point in your operating system before starting editing, if your country is using comma for decimal marker.
Copy/Paste, editing in Excel, then replacing and filling the missing cells in the Browse Data tab. And at the end File/Write Changes to save the changes to the database.
To get the database off from HA, I used the ssh addon, I stopped Core, by the ha core stop command, and ftp-ed the DB to my Windows PC. Then I did the editing an and ftp-ed back the DB to HA, then I started Core, by the ha core start.
I hope it helps someone…
HI Gys,
Any idea what i might be doing wrong?
i found the points at which the state at “states” went wrong
which then peaked value in statistics_short_term. Then I subtracted the hight of the peak
like this:
> UPDATE "statistics_short_term"
> SET sum = sum - ( 318.34999999999934 - 67.65999999999926 )
> WHERE metadata_id = 223 AND sum > 300
before update it looked like this:
| a | b | b | a | b | diff |
|---|---|---|---|---|---|
| 3186966 | 2022-05-22 08:50:10.796862 | 242,32 | 61,85 | 223 | |
| 3187065 | 2022-05-22 09:40:11.251040 | 244,56 | 306,41 | 223 | 244,56 |
| 3189985 | 2022-05-22 12:20:10.316715 | 249,38 | 311,23 | 223 | |
| 3190076 | 2022-05-22 12:25:10.484701 | 249,44 | 560,7 | 223 | 249,47 |
| 3190442 | 2022-05-22 12:45:10.261957 | 249,56 | 560,82 | 223 | |
| 3190537 | 2022-05-22 12:55:10.347581 | 249,81 | 810,71 | 223 | 249,89 |
| 3195914 | 2022-05-22 17:55:10.312823 | 250,68 | 811,58 | 223 | |
| 3195999 | 2022-05-22 18:00:10.346917 | 250,69 | 1062,27 | 223 | 250,69 |
but for some reason this did not reflect the energy chart,
and it actually screwed up next day making up for the “missing” 994kWh
Energy chart uses long term statistic, and you have fixed your short term one only according your formula.
Did you change the sum in both short term and long term statistics of the sensor?
It’s probably much easier to fix via the UI though (from the STATISTICS tab)
fair enough , so short are short_term (5min) but long term are called “statistics” (1hr).
just noticed this 
thanks…
so: history chart is from “states” , energy dashboard from “statistics” and what uses this short term then?
STATISTIC TAB - where is this ? - OK just found this either i see there is this 5 min resolution
Long term statistics (each hour) are calculated using the short term statistics
You should be able to click on the icon on the far right for your problematic icon. go to the data and time when the issue occured and decrease the sum by the required value to get it to what it should be, which will then adjust the sum from that point to now
as i messed up a little with short term in sql already i had to finish it like that. but the second sensor from statistic tab went perfectly.
Anyway, all fine now, looking good again
Thanks for all assistance!
EDIT,
I’ll just put a link here because it’s nicely described and will help before messing up with ale SQL mining
4 Likes
I’m sruggling with correcting the data 
What I have is Huawey SUN2000 PV inverter which gives power but not energy. For Energy i use integration platform and calclulate sensor_pv_energy:
- platform: integration
source: sensor.sun2000_10ktl_m1_sun2000_12_bt2150240752
name: pv_energy
unit_prefix: k
round: 2
Now this night, my inverter sent some wrong wattage peak :
and because of that pv_energy sensor was calclulated wrong (this is already post db changes picture:
And of course energy dashboard is now off with a peak of 300kwh solar power.
I used this guide here and corrected the pv_energy values in:
- States
- statistics
- statistics_short_term
Everything was ok, but then probably integration platform calcluated again the difference and wrong numbers wehre in states, statistics_short_term and statistics again.
Then I thought that I should update the sensor.sun2000_10ktl_m1_sun2000_12_bt2150240752 wrong value as well, because integration platform uses it. Only place in db i saw these values, was states table. I corrected the data there, but in the UI it still shows the peak with wrong data and pv_energy is calclulated wrong again by integration platform.
So my question is, what to look more to solve this issue?
EDIT: Interesting is that i can’t see this wrong data in the dev toold statistic tab:
I have played with the hours and always 0. Maybe it’s because of it’s the “integration platform” sensor?
As you see in Developer tools you can correct the statistics, aka long term statistics.
If you corrected the statistics for your energy sensor then it should show the right values in your Energy tab.
You shouldn’t care about short term statistics as it will be discarded after a few days unless you are storing all values in an InfluxDB to display it with Grafana, then you should correct your value there.
Obviously your pv_energy short term statistics has been fudged not at 4AM, but after 8AM, so you have made some magic wrong there.
So what i just did:
-
Stopped Home Assistant docker
-
states corrected
update states set state = round(state - 301.71, 1)
where
entity_id ='sensor.pv_energy'
and state_id between 12580414 and 12580568;
Result: no wrong states:
- statistics_short_term corrected
update statistics_short_term set sum = sum -301.63
where
metadata_id == 8
and id between 3990052 and 4001703;
and
update statistics_short_term set sum = sum -301.63
where
metadata_id == 8
and id between 3990052 and 4001703;
Short term stats clean:
- statistics corrected
update statistics set sum = sum -301.63
where
metadata_id == 8
and id between 333605 and 334505;
and
update statistics set state = state -301.63
where
metadata_id == 8
and id between 333605 and 334505;
Result:
Checked PV states with a command:
SELECT *
FROM "states"
WHERE
entity_id ='sensor.sun2000_10ktl_m1_sun2000_12_bt2150240752'
and last_updated between '2022-10-11 00:00:00' and '2022-10-12 23:59:00'
There is no wrong data there, as I cleaned it this morning.
I’ll start the HA now, and post what happens in db
1 Like
If you see it there, then it means you can fix it through developer tools. You just need to find the hour where it is happening and set it to 0.
How does the short term looks?
just did it myself, works as a charm 
You just have to find the sensor and the date🤔
I think you have the wrong sensor?
Try looking for Consumed Solar (if that is the one that’s wrong)
Its the excact senor:
As you can see from the picture, I changed the integration and from now on, i can probably correct the issues, as this integration offers energy sensor as well.
My guess is that i cannot correct, beaduse the old sensor was the integration sensor, which cannot be corrected:
- platform: integration
source: sensor.sun2000_10ktl_m1_sun2000_12_bt2150240752
name: pv_energy
unit_prefix: k
round: 2
Bad thing is that i can’t delete the old sensor from Energy Dashboard, because i will lose all the previous data. So i just have to keep in mind that there is this 300kwh difference. One thing i can probably do is change the pv_energy senor to some static number and correct the bad data in db.
Have you changed the minutes as well? Because every time when you show this picture you show only the whole hour.
The data will be stored somewhere during the hour, not just at the start or end of the hour.
So i commented out the integration sensor in configruation.yaml , corrected the DB, switched to new pv integration and looks like it’s solved.
And yes, i changed minutes as well.