Data is gone but the graph is still showing over 600kwh 
Also sensor data is unchanged
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 
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 
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:
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
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