Energy Management in Home Assistant

OzGav · December 31, 2021, 12:26pm

He probably should move one of his CT clamps to the main feed in wire and then use math to calculate the circuit that is now not monitored directly.

luisgarcia87 · December 31, 2021, 1:45pm

Can you show the graph for the full day. Like this

I want to see the full behavior for the day

sk94 · January 1, 2022, 1:48am

Sure! Here’s a full day

sk94 · January 1, 2022, 1:49am

Right I get what you’re saying, I can make a 2nd measurement that’s only positive when feed in solar < 0 and expose that to home assistant. I’ll give that a shot soon

sanjeewa.samaranayak · January 3, 2022, 10:24am

Hi
I need help to figure out what is going on with the Ebergy Dashboard which I have given up on for some time as I can get correct info from my Growatt Dashboard and also via Grafana.

I have battery and Solar. Problem is that Grid Consumption in energy dashboard is wrong. Energy Dashboard is making things up and not using the sensor info I added for it to use. Everywhere else the same sensor within HA is showing up correct value and only Energy Dashboard is showing wrong numbers as it looks like it does a wired calc without using the sensor info. The Grid consumption is actually 1KWh today (deleoper tool, growatt integrtation) but in HA Energy Dashboard is showing it as 24.6KWh???. This is not a new problem and has been wrong for months and that is why I stopped looking at energy dashboard for long time. What i say Grid Consumption it is the imported from external grid today by my house (internal use and in some cases battery charging). The growatt and my electricty bill impots always match within 2% error

Does anyone know what logic HA is using to do various calculation and what it calculate and what it take based on sensors we add? I cannot find any post that explain the logic applied in the calculations and What each of these sections - Grid Consumption, Return to grid, Solar Panels, Home battery storage expect user to include (well I have use the common sense based on what description says but may be that is not what developers expect). Someone need to spell out the logic as I am sure each system vendor use their own terminology as such just adding name tags to add sensors in HA dashboard config is not enough IMHO and is confusing to users.

The Sensor I have added to Dashboard are- All from Growatt official integration and with additional attributes added by me via customize.yaml. The details are taken from Developer tools-> States for entities:
a) Grid Consumption-> sensor.ntch951021_import_from_grid_today_load_charging
unit_of_measurement: kWh
device_class: energy
icon: mdi:solar-power
friendly_name: NTCH951021 Import from grid today (load + charging)
state_class: total_increasing
last_reset: ‘1970-01-01T00:00:00+00:00’

Return to Grid-> sensor.ntch951021_export_to_grid_today
unit_of_measurement: kWh
device_class: energy
icon: mdi:solar-power
friendly_name: NTCH951021 Export to grid today
state_class: total_increasing
last_reset: ‘1970-01-01T00:00:00+00:00’

Solar Panels-> sensor.ntch951021_solar_energy_today
unit_of_measurement: kWh
device_class: energy
icon: mdi:solar-power
friendly_name: NTCH951021 Solar energy today
state_class: total_increasing
last_reset: ‘1970-01-01T00:00:00+00:00’

Home Battery Storage:
NTCH951021 Battery charged today
unit_of_measurement: kWh
device_class: energy
icon: mdi:solar-power
friendly_name: NTCH951021 Battery charged today
state_class: total_increasing
last_reset: ‘1970-01-01T00:00:00+00:00’

NTCH951021 Battery discharged today
unit_of_measurement: kWh
device_class: energy
icon: mdi:solar-power
friendly_name: NTCH951021 Battery discharged today
state_class: total_increasing
last_reset: ‘1970-01-01T00:00:00+00:00’

luuuis · January 3, 2022, 11:48pm

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.

This behaviour is explained more precisely in New sensor state class: total_increasing | Home Assistant Developer Docs.

sanjeewa.samaranayak · January 6, 2022, 7:27am

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

sk94 · January 7, 2022, 12:11am

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!

hdehaseleer · January 7, 2022, 1:32pm

Hello

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!!

Fete · January 8, 2022, 5:06pm

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).

Fete · January 8, 2022, 5:08pm

Actually, that’s NOT what I’m after. Da-n lazy teenagers!

But graphically/mathematically, YES, that’s what I’m after. But replace “Teenagers doing expensive things” with “Washing machine”

e-raser · January 9, 2022, 1:05am

arkoko:

You can try serach from states table:
select state_id, state, last_changed, last_updated, old_state_id from "states" where entity_id="sensor.your_sensor_name_energy" order by state_id desc;
Or from statistics, if this graph from energy tab:
select * from "statistics" where metadata_id=(select id from "statistics_meta" where statistic_id="sensor.your_sensor_name_energy") order by id desc;
Or from short_term statistics table:
select * from "statistics_short_term" where metadata_id=(select id from "statistics_meta" where statistic_id="sensor.your_sensor_name_energy") order by id desc;
And if you find abnormal row, you can delete it by id number or update to the normal value, like this:
update "states" set state="1472.85" where state_id=244913;
update "statistics" set sum=4095.6 where id=11505;
update "statistics_short_term" set state=177.93 where id=311434;

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!

arkoko · January 9, 2022, 8:32pm

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

e-raser · January 9, 2022, 9:33pm

Thanks! That sounds really very simple
I’ll give it a try - with a few test rows at first and only after a fresh database backup

WolfsW3lp3 · January 10, 2022, 1:05am

Hello,

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.)

etc.

And exactly on those dates I get horrible values in my energy dashboard:

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.

Poker1410 · January 10, 2022, 2:12pm

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.

Hope it makes sense
Thanks in advance

OzGav · January 11, 2022, 5:34am

Please read up this thread there are heaps of these questions.

wattmatters · January 12, 2022, 9:12am

True but frankly the means to correct erroneous data are hardly user friendly.

allocater2 · January 12, 2022, 7:20pm

You can set up a template sensor that blocks the drops.

sensor:
  - platform: template
    sensors: 
      my_new_sensor_sanitized:
        value_template: "{{ max( states('sensor.sma_sensor_with_drops')|float , states('sensor.my_new_sensor_sanitized')|float) }}"
        unit_of_measurement: kWh

then kick the “raw” sma sensor out of your dashboard and add the new sensor instead.

To make it selectable for the energy dashboard you will have to add customizations to it like:

        state_class: total_increasing #or measurement
        device_class: energy
        last_reset: '2000-01-01T00:00:00'

WolfsW3lp3 · January 12, 2022, 9:56pm

Hey thanks very much for your reply.
I created a sensor now which looks like this:

- sensor:
    - unique_id: total_solar_producing
      name: 'total_solar_producing'
      device_class: energy
      state_class: total_increasing
      unit_of_measurement: "kWh"
      state: "{{ max( (states('sensor.pv_gen_meter')|float + states('sensor.pv_gen_meter_2')|float) , states('sensor.total_solar_producing')|float) }}"
      availability: >-
        {{ states('sensor.pv_gen_meter') not in ['unknown', 'unavailable', 'none', '0'] and 
           states('sensor.pv_gen_meter_2') not in ['unknown', 'unavailable', 'none', '0'] }}

I have two solar seperate Inverters for two solar different solar areas, thats why pv_gen_meter and pv_gen_meter_2.

Is this correct like I did it?