-
Notifications
You must be signed in to change notification settings - Fork 2.6k
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Cannot fix invalid energy dashboard data #17010
Comments
Issues home-assistant/core#94896 appears to be similar. |
Does the sensor have I'm not sure how to fix it now, but I don't think it would have happened if it was |
Inside HA, Developer Tools / States shows me
state_class: total_increasing
unit_of_measurement: m³
icon: mdi:fire
friendly_name: Gasverbrauch
device_class: gas
karwosts ***@***.***> schrieb am Fr. 23. Juni 2023 um 16:04:
… Does the sensor have state_class: total ?
I'm not sure how to fix it now, but I don't think it would have happened
if it was total_increasing, which is probably more correct.
—
Reply to this email directly, view it on GitHub
<#17010 (comment)>,
or unsubscribe
<https://github.com/notifications/unsubscribe-auth/AEGI2E4IVUGFSZGJYNWICILXMWO55ANCNFSM6AAAAAAZRTN6AM>
.
You are receiving this because you authored the thread.Message ID:
***@***.***>
|
Ah ok, nevermind then. I was hoping that was the issue, but it must be something else. |
Unfortunately what you have is not a "bad sensor reading", which could be fixed in the statistics ui, but the result of a HA bug wherein the cumulative sum for a statistics sensor got accidentally reset when it should not. I believe the underlying bug has been fixed, but you're left with a corrupted database. It is possible to fix this but you will have to do it in raw SQL, which is possible in haos if you install the sqlite addon. If you want me to walk you through the steps to do so let me know if you want to take that approach once you have the addon installed (or if you already know how to access the database in another way). |
I'm going to leave some notes here on how I fixed this, assuming a sensor with
e.g.
From here you want to note two things, the
e.g.:
After you run this you should get a terse result like "N rows updated". At this point you can refresh your web browser, and the negative spike should be gone. |
To find the row that has the drop (step 6), better use a query like below, that finds the row(s) where the value for sum is smaller than that on the last row before it. |
Ok, many thanks for @karwosts and @rrozema, I fixed it although me setting was a bit different. I use mariadb and phpMyAdmin and can confirm that the above steps work here as well. My steps (see #17010 (comment)) were ...
6 and 7) Last step in my case was
|
Do you happen to have a link to the bug? As soon as the sensor starts sending data again, I again receive a huge negative reading and can start all over again ;-) |
Actually now I'm not so sure it has been fixed. I was thinking of some issue specifically related to riemann sums being reset when they shouldn't (don't have a issue link handy), but now I'm wondering if this could happen to any sensor. I'm not sure yet what's triggering it. |
Following query can give you some more insight in what exactly the contents of the statistics and the statistics_short_term table are. The query lists all data points recorded on a particular day for one particular statistic, adding to the rows properties the previous row's state and sum, plus the difference between this row and the previous one for each data point.
I have seen examples where a device was temporary unavailable. Depending on the timing I think this can sometimes make the recorder behave strangely: It seems to sometimes reset the running total to 0, and then when the device comes back available and again reports the previous value the recorder can record a very large difference. I think a scenario like this can somehow result in the recorder registering a difference in a single step that is as large as the difference between the reset value (0 most of the times) and the current value of the continuously increasing meter value. I have reported an issue like this for the HACS sankey-card (a very nice, but so far un-official energy card). In that situation I could exactly explain what happened. I have however not yet found a clear example yet where I could catch the the official energy cards doing something similar. But I too do see some un-explainable spikes plus negative growing values sometimes (the negative growth seems like rounding errors to me, as they are usually very small values).
So, by no means I think I can give an explanation of the behavior you observed, but I thought I'd give you the queries so you can have a better look at the underlying data the recorder produces and the energy cards read. In fact: That last query just gave me a rather strange result:
The device apparently raised the state at 12:10 UTC from 3.44 to 8.85, then at 14:10 UTC the state changed back to the original value, from 8.85 to 3.44. The recorder however responds strangely to this change: at 12:10 UTC the sum is raised from 1.62 to 6.76 , then at 14:10 UTC the sum changes from 6.76 to 10.2. This device is a FGS223 by Fibargroup Firmware: 3.2, not a cheap neo coolcam device. This device is not supposed to report such state changes, but the fact that the recorder responds by adding 5.14 + 3.44 to the running total (sum) does not seem correct to me either. At least it results in an unrealistically high value for usage of 8.58kWh in 2 hours for 3 led lights: I think I've seen so far 3 different incorrect behaviors: I have a feeling that 1 and 2 are actually the same issue, but it depends on timing whether or not the change falls into a period that can be edited in the statistics tool. And 3 feels like a rounding error to me: when looking at the values using sqlite3, they are often very small negative numbers close to rounding points. Also: the statistics tool has a rounding issue itself too. It will not write a change to the database if the change is less than the 2 decimals the tool allows to enter. i.e. it won't let you correct an error that is > -0.005 and < 0.005 because it decides wheter or not to write the changes back to the database based on if the (rounded) presentation value changed, not the actual value read from the database. |
Ok, having run the SQL queries once did not do the job :-/. If the sensor reports data again after a while, the energy dashboard again shows a huge negative value. Given that I‘ve upgraded to 2023.07 in the meantime, I doubt that any bug has been fixed. I cannot run the query in #17010 (comment) as it reports errors. „main.statistics“ does not exist - I fixed that by just using „statistics“. The next error was „Function homeassistant.strftime does not exist“ and here ends my SQL knowledge ;-) Do I have to fix anything in |
@swa72 : I would not recommend making too much changes in the statistics table based on the above info. The information from me (and probably that from karwosts as well) is derived from looking at the contents of the database tables mostly, not from intimate knowledge of the workings of the recorder component. i.e. There may be other functionalities involved that were not known to me (or karwosts) at the time of writing. At the very least do make proper backups of your tables if you make any changes! Myself I use the queries to find where exactly the 'bad data' is, then always first try to use the statistics development tool. Only if there is really no other option I do any updates to the tables using update statements, and even then I only do this if the errors in my graphs are too bad to ignore... One trick I found to work in some situations where the tool doesn't let me edit the value is to adjust the statistics twice using the statistics tool: first adjust the statistic into some fictive value like for example 123, then later re-edit it to the value I actually want it to have, like 0 for example. This sometimes fixes the rounding errors that the tool seems to suffer from. b.t.w. do you happen to have another database than sqlite configured for your home assitant instance? Because that is the only thing I can think of that explains why you had to remove the "main." from the query and do not have the strftime() function. From my observations, the statistics_short_term table seems to only be used to create the graphs in the 'history' views. This data does get cleaned after x days (as configured in your settings) so any oddities in there will be gone in x days anyway. The energy cards however seem to read from the statistics table only. So I think you would not need to update the statistics_short_term table to fix any issues in the energy cards. |
@rrozema Thanks for the infos and you were right in assuming I use another db. I switched to MariaDB some time ago. |
Hi, The "new" sensor value is exactly the same as before the unavailable (i can see it on mqtt browser ) ( so i don't get any row where the value for sum is smaller than that on the last row before it.)
ps : i use this sqlite command to modify ( my sensor value doesn't change since may as it's used only in the winter)
on another sensor, i use this kind of sqlite command:
|
The graph shows the difference between the [sum] values for one entry and its 'previous' entry. When you delete one entry, the next available entry before that will become the new 'previous' entry. Because this new 'previous' entry has (almost) the same data as the one you just deleted, you'll get exactly the same spike, only over a somewhat longer time frame. |
I got the exact same problem. One of my Plugs reported a negative value after being not plugged in and being plugged back after several weeks, which now completly breaks my energy usage, as the device in question "generated" energy when it came back. I've used the SQL magic in this thread to find the value for the energy cost sensor and set it to the last value that makes sense, but every hour roles over and the dashboard gets refreshed I get another 0 entry (device is online but powering nothing), which, again, breaks the whole dashboard. How can this be fixed? |
@lolmachine4 - Can you expand your comment with some thorough datapoints? I'd be curious exactly what data is in your SQL table each hour, and exactly what you mean by a "0 entry". |
@karwosts I will certainly try! Please bear in mind: I'm an absolute novice when it comes to SQL an thelike. All I can is watch and observe. So, this is what the dashboard looks like right now. I hope this helps! |
For now I think I will suggest lets stop trying to modify the SQL tables directly, I think the same thing can be achieved by using the "fix a statistic" dialog in developer tools. Lets let everything rest for a few hours, then take a picture of your dashboard energy graph that shows the erroneous datapoint. We can then see if there's an update we can make in statistics devtools that will remove it. |
Having run the DB fix a couple of times, I sadly can confirm that the error re-appears: It is a gas sensor that doesn't do much these summer days. But once some gas gets consumed, HA records it incorrectly (6.3499) resulting in a huge negative spike from the previous sum I should add that the entire thing worked flawlessly for more than a year, including reboots, updates and somesuch. Checking Developer | Statistics doesn't help (e.g. removing the 0.03 reading) either. I would like to try the following:
and see if that works. Sadly my SQL knowledge is not sufficient to create that query. |
@lolmachine4 - so if you do "fix statistic" dialog at ~21:40 for august 6, it shows a 0 there? And if you look on energy dashboard, in the 21-22 hour for august 6 it shows a bar for +30kWh at 21-22pm? If that's the case you can try "fixing" the 0 to change it to -30, and I think that may remove the erroneous bar. Note that we're actually straying pretty far from a frontend issue here since all this is actually wrong in the backend database. I'll try to finish up getting a bandaid on some of these issues but this probably should be closed and moved to a core issue. |
@karwosts |
Try change the datapoint at 22:00 from |
For now I'm going to close this as all the issues reported here are actually core issues with statistics, which is not really something can be fixed in frontend. If you get future cases of seeing long term statistics values getting reset unintentionally, I would open a core issue for long term statistics. |
I noticed by accident that my huge negative spike of -5800m3 suddenly showed up in Developer | Statistics. I zero'ed that value and, fingers crossed, everything looks good. Odd. |
This really helped me, thankyou. |
yes it's working for negative spick but not in case of a bug ( see my comment in #17010 (comment) ) |
Checklist
Describe the issue you are experiencing
I have a sensor for my gas usage (ESPhome) which has been working nicely with the Energy dashboard until about a week ago. At that day the Energy dashboard shows a huge negative value (I may have updated HA on that day, now running 2023.6.2).
This should be quick to fix, by going to to Developer | Statistics and remove any bad reading.
However, there is no such reading in that timeframe ;-/
I've searched that evening in 5min steps to no avail. Do I have to go into HA's internals to fix this?
Here is my ESP config, just in case ...
Describe the behavior you expected
I can correct all values thru Developers | Statistics of that particular entity.
Steps to reproduce the issue
...
What version of Home Assistant Core has the issue?
Home Assistant 2023.6.2
What was the last working version of Home Assistant Core?
No response
In which browser are you experiencing the issue with?
No response
Which operating system are you using to run this browser?
Win 10
State of relevant entities
No response
Problem-relevant frontend configuration
No response
Javascript errors shown in your browser console/inspector
No response
Additional information
No response
The text was updated successfully, but these errors were encountered: