Energy Management in Home Assistant

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?

allocater2 · January 13, 2022, 6:01pm

Seems good. Is it a template sensor? The test will be if it produces a smooth line without drops and if you can add it to the energy dashboard.

muzzak · January 14, 2022, 3:46am

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 ?

ziepoeskenetnie · January 14, 2022, 10:02pm

My solaredge import and export entity states are unknown. What’s happening here?

e-raser · January 15, 2022, 10:12pm

I think I finally fixed my Energy Management in Home Assistant - #1067 by e-raser misconfiguration from 2021-11-14.

I updated the solution post Energy Management in Home Assistant - #1127 by e-raser and added some learnings from that especially in section 1.

WolfsW3lp3 · January 19, 2022, 11:17pm

Heyho,

sadly it didn’t help.
Happened again the day before yesterday

Any ideas?

toreamun · January 20, 2022, 12:33pm

Norwegian user (and soon swedish) might find this custom integration useful: AMSHAN

The integration can parse data from all norwegian meters. You need a cheap M-BUS USB slave device or another adapter supporting MQTT (like Tibber Pulse) to use the integration.

ziepoeskenetnie · January 24, 2022, 4:03pm

Is it possible to use 1 entity for consumption as well as back to grid? I have 1 Shelly EM ct clamp installed, is shows negative values when power is going back to the grid and positive values when i consume power. See pictures below:

Screenshot_20220124-160231_Home Assistant

In docs i see net_consumption how do i use this?