Make sure the values returned by your sensors that are marked as total_increasing are stable over the course of the day (if you graph the values in the History tab this will be pretty obvious). As I understand it, what HA will do is examine the values on an hourly basis and if the returned values are less than the previously observed value that will lead to the same energy being accounted for multiple times.
If the value can’t be obtained at some point then the sensor needs to be returning unavailable rather than a zero value or that could throw off all the calculations.
Thank you very much. I reviewed both import and export to grid numbers for the KWh sensors that is provided by Growatt integration and it is clear that the numbers are not stable as you have indicated- meaning 2Pm say 2Kwh and 2.20Pm 1.8Kwh and then 2.40Pm 2.1Kwh etc. This very clear when i use a Grafana table to review all recoded numbers for sensors.
Funnily enough I could not find any other user with growatt integration complaining about this issue. Anyway what I am trialing now is the use of export and import Kw sensors available in Growatt integration (not the import and export for the day sensor which is Kwh) and then use a template to convert it to W sensor and and then use that in Riemann Sum Integral to create new kWh sensors for import and export to Grid. I am still monitoring the numbers and only in couple of days I will know if it work.
Thank you very much for your analysis of what the problem was
I created another measurement similar to Solar Feed in (but only counts positive values from net house draw) and the energy dashboard is working fine thanks for your help!
I just bought a small Shelly Energy meter with two CT clamps.
It was extremely easy to add and to configure the Shelly integration. And also very easy to add these two clamps via “Configuration - Energy”
And now I have already the daily consumption in a bar graph for each hour.
Very nice work. Thanks a lot.
But the bar graph is for a period of 1 hour. Is there a easy way to have a bar graph for each 15 minutes?
Because here in Belgium, the energy company will make our bill each month, but they will base that bill on the highest kWh consumed in only 15 minutes!!
Imagine that I make the error to turn on two “power hugry” devices in the same 15 minutes, and turn them off for the next 45 minutes. And that I make this error only once for that month. My energy bill will be high for that month!!
Thanks, but that does not solve my problem. When I DO add individual devices to the grid section, it makes the bars look like I WANT them to look (though the meassured consumption is wrong ofc), but I want the bars to brake down how much the individual devices that are connected have used of the total bar (kWh used per a specific hour).
Thank you very much, that was a kickstart for my “finally fix the energy dashboard” job!
I could find the “accident hour” with your slightly adjusted select statement (using some loooong time ago SQL basic knowledge ):
SELECT * FROM "statistics"
WHERE metadata_id=(SELECT id from "statistics_meta" WHERE statistic_id="sensor.energy_total_total") AND created LIKE "2021-11-14%"
ORDER BY id ASC
LIMIT 100;
Now I face the following situation which makes the whole operation a bit tricky:
It’s not just one row which needs to be updated, but ALL rows after the “accident hour” up to now (today):
while state column values proceeded normal (sensor values), the sum column values (which is the one used for the energy dashboard statistics) proceeded with the added value. Visually:
id created metadata_id start mean min max last_reset state sum
425609 2021-11-14 16:00:11.111377 133 2021-11-14 15:00:00.000000 NULL NULL NULL 1970-01-01 00:00:00.000000 364.33 224.10999999999999
425773 2021-11-14 17:00:11.159665 133 2021-11-14 16:00:00.000000 NULL NULL NULL 1970-01-01 00:00:00.000000 364.49 224.27
===> sum column rows above (before) this line are fine, sum column rows below (after) this line are *WRONG*<===
425937 2021-11-14 18:00:11.045548 133 2021-11-14 17:00:00.000000 NULL NULL NULL 2021-06-04 00:00:00.000000 364.67 588.99
426106 2021-11-14 19:00:11.148599 133 2021-11-14 18:00:00.000000 NULL NULL NULL 2021-06-04 00:00:00.000000 364.86 589.1799999999998
Therefore the following questions:
MOST IMPORTANT: How to do a mass change of the sum field of all rows (where id greater than 425773 and metadata_id=133) up to today including an on the fly calculation? It needs to use the old value and reduce that by a fixed amount. Is there a SQL UPDATE statement for this (calculation included)?
Example: value before = 500, value after update should be 200. Sth. like “update value minus 300” is what I’m looking for. Update 2022-01-15: UPDATE statistics SET sum=sum-300 WHERE metadata_id=133 AND id > 425773; according to Energy Management in Home Assistant - #1128 by arkoko seems to have worked without any notable side effects. Energy Dashboard is now showing correct data again (finally). Thanks @arkoko ! Update 2 hours later:
There were some side effects. Like no generation of new hourly bars in energy dashboard. Reason: I forgot to also update the sum values of the affected total sensor in the statistics_short_term table. So an additional UPDATE statistics_short_term SET sum=sum-364.54 WHERE metadata_id=133 AND id >= 3703032; did the trick where the id was the oldest one available in the table for that sensor.
Learnings: both tables need to be updated. Values in statistics_short_term (earliest available row depends on recorder keep_days or purge settings, in my case 14 days so 1st of January was the oldest available row) is generated every 5 minutes, statistics only hourly (which should be called “statistics_long_term” as it records all values from the beginning and does not purge it. Only this way e. g. the Energy Dashboard can show graphs of numbers older than the recorder keep_days / purge setting.
Will the rest of the energy dashboard (week, month and year stats for example) “fix” itself after the update? Or do I need to fix other parts as well?
I believe first is true (will fix itself) based on few spot checks aka “reverse engineering” :
a) year: the energy total value in the year 2021 view is simply the last value of column sum with created = 2021-12-31 23:00:12.986483.
b) month: the energy total value in the month November 2021 view is simply calculated by "last value of last day of current month (e. g. November)" minus "last value of last day of previous month (e. g. October)". I checked this with December and it matches perfectly. Update 2022-01-15:
Currently (after performing the database changes) no negative side effects (or any at all) discovered.
What about the costs? My energy total sensor is configured to use an entity which keeps track of the total costs.
→ based on the database entries I’m very sure I configured this after the “accident” according to first row for the corresponding metadata_id of my total cost sensor. So at least that’s fine, nothing I need to care about.
Would I need to fix my InfluxDB data as well?
Currently I pump everything into the InfluxDB. As of today I can’t imagine why I even would like to use that energy dashboard visualization data from InfluxDB.
And: based on a quick check those data isn’t even stored, InfluxDB “only” catches all sensor data. That energy dashboard data isn’t from states table (objects like “total power consumption entity” for example) but from statistics table - that data is not copied to InfluxDB.
So with a proper command for question #1 I should be almost there… SQL experts like @arkoko welcome!
It’s easy: UPDATE statistics SET sum=sum-300 WHERE metadata_id=133 AND id > 425773;
This should work, but I do not take responsibility for data loss. Do everything at your own risk
I have a small problem with my energy management dashboard.
Sometimes the SMA Integration gets wrong values from my Devices.
Like here, sometimes it just collects the value 0 for total energy absorbed from the external grid.
Same for all the other sensors they are just jumping to zero. (Power absorbed from external grid or total produced power of the solar panels.)
I haven’t figured out yet how to delete only those values out of the recorder, so I keep all my other sensor values.
But is there a way to delete those values and to prevent this from happening in the future?
I really have no idea what exactly is happening and why it jumps to 0 sometimes.
Hi all
I just set up my Energy dashboard and noticed that, every now and then, the systems returns rogue values (possibly because my sensor monitor power instead of energy.
My question is: is there a way to access to the SQL database where the historical data is stored and change the values manually?
I use Smappee that measures power and calculate energy but there is no easy way to import the energy values from its servers. My idea iskeep an eye on Smappee and, if required, amend the values in Home assistant.
Hi, I’ve been using the Energy Management for a while and it’s all been working well. However the other day I had a power failure and the data feed from the inverter to HA was lost for a few days. I have reset the data feed and although it is now working again, HA is now missing the data for the down period.
I have the missing data in csv format from the inverter, so I was wondering if anyone knows how to load the missing data into HA ?
thanks for your help!
):
MOST IMPORTANT: How to do a 
Will the rest of the energy dashboard (week, month and year stats for example) “fix” itself after the update? Or do I need to fix other parts as well?
:
