Energy Management in Home Assistant

[arva]

Is there a way to manually correct the data on Energy Management? I had some networks issues yesterday and my Shelly EM3 was offline and came online one yesterday and twice this morning. So my daily, weekly and monthly are quite a bit off now. See the screens:

Day before yesterday, when everything was ok:

Yesterday:

Today:

I would be ok, if i can delete those two spikes and no data for those two days.

[arva]

Turned out it was caused by Shelly EM3. After restar sometimes it will be unavailable and because of that calculation that sums three phases will be 0 for a brief moment and then back to where it really was. Trick was to add availability_template to the template sensor. More here: Shelly 3EM 3-phases Energy sensor - #56 by arva

But now i need to delete those spikes and I don’t know where.

EDIT: Here is a solution how you can delete from the DB: Energy Management in Home Assistant - #455 by markss

[arva]

@markss

For those wanting to dig a little further into their Energy visualisation not working, you can use the “SQLite Web” addon to look under the covers at the statistics and how it works. It appears some entities from plugins will have all the appropriate attributes to appear in here, if yours aren’t, check the posts above that mention last_reset:

        attributes:         
          last_reset: '1970-01-01T00:00:00+00:00'

To get started, you will need to install the SQLite Web addon and open it, you should see something like the following after choosing statistics_meta, then content.

statistics_meta appears to be a master lookup table of all your entities that statistics are being collected on, in my case I am interested in #8:

If you then choose statistics - content you will probably see lots of entries however, I have used the query function command: SELECT * FROM "statistics" where metadata_id = 8 to pull up all entries associated with my solar feed in - there is only one because I have been trying to get my OpenEnergyMonitor configuration working despite the lack of documentation.

If you want to reset the statistics of an entity, you can do it here also, because I have been using a “smart plug” in a variety of places I wanted to reset it so it has clean data for the energy integration.

First: I manually locate it’s ID via statistics_meta and find out it is #2.

Then go to the “Query” tab and to make sure I have everything as correct as possible, I type in:
SELECT * FROM "statistics" where metadata_id = 2 and click execute.

DANGER
If that shows the results that I am expecting, to deleted it, I slightly change the command to:
DELETE FROM "statistics" where metadata_id = 2 and click execute.

It then appears that statistics are generated hourly so that is why you need to wait 2 hours for the visualisation to show any information, but if you go in via the SQLite Web addon you should be able to see some sort of value after 1 hours (on my system it shows as on the hour)

To make this slightly more discoverable, this post is about:
OpenEnergyMonitor (emoncms), EnergyManagement, Statistics, SQLite Web, database cleaning

Thanks for sharing the instructions!

My issue was that Shelly EM was unavailable few times after restarts and template sensor that sums up three phases went to 0 and theb back to real value.

When i investigated database with SQLite Web I saw the problem with some 0 rows and sum lines that were affected. I deleted the 0 rows and corrected the SUM values with UPDATE. Now everything in DB looks good, but Energy Dashboard still shows the wrong numbers.

Any ideas, where the Energy Management gets the (wrong) data?

EDIT: If anyone is using HA in a Docker stack, SQLite Web works neatly with Docker Compose:

  sqliteweb:
    image: tomdesinto/sqliteweb
    ports:
      - 8089:8080
    volumes:
    - ${USERDIR}/docker/homeassistant/home-assistant_v2.db:/db/home-assistant_v2.db
    command: home-assistant_v2.db    

[kds69]

I have posted a new topic in community forum to suggest to add Water Heating as new source/consumption of energy on the top of Grid, Solar and Gas, for those who are connected to district heating or have solar water panel.
I am not speaking about water consumption…
see Add Water Heating as source of Energy

Cheers

[nemezis336]

Hi such a problem and I can not find a solution
database - mariadb.
1.After restart, HA added the entire value of the entity to me, not just the measured hour. How to avoid it, is there a way?
2. Is it possible to remove this result from the database so as not to disturb the whole?

[VDRainer]

There is a nice topic about this.

How to fix statistics data (e.g. energy data)

Hey I have managed (I think) to solve the issue which was bothering me for a long time and I have decided to share the solution with you guys. I hope that someone finds it helpful. Problem I have OWL energy sensor which sends the data via RF433. The problem is that sometimes the received values are not correct. Pretty often I get “0” as the energy_total and once the value jumped significantly by 78M. [image] I don’t have the sensor data to investigate what has happened that day and what wer…

[maximtg (Maxim)]

Hello,
I am struggling to monitor energy in KWh from Tuya wifi plug with energy monitor.

Home Assistant recognize the value of the plug without problem:

So I created this sensor to convert the value of “Power (W)” in KWh:

- platform: integration
  source: sensor.fridge_power
  name: KWh Fridge
  unit_prefix: k
  unit_time: h
  round: 2

Then I personalized the entity adding two custom field: state_class and last_reset.

In the developers tools it looks like this:

state_class: measurement
source: sensor.frigo_power
unit_of_measurement: kWh
friendly_name: KWh Fridge
icon: mdi:chart-histogram
device_class: energy
last_reset: '2000-01-01T00:00:00'

The problem is that I saw the entity in “Energy Monitor” on Home Assistant but with wrong value because it shows me a daily consumption of 3 KWh instead on Smart Life app I saw a daily consumption of 0.54 KWh.

I tried to convert by myself the value of the watt in kwh and HA is 100% wrong. Even if I add “method: left” or “method: right” nothing changed.

What am I doing wrong ?

Thanks.

[jmbinette]

Hi,

Anyone can help me build a dynamic price entity ?

Per invoicing period, first 40kWh * days (For example 62 days @ 40kWh = 2480kWh), the price is .06159$.
Remaining kWh are .09502$

And I have a “grid fee” of .41168$ per day

Anyone can help ? (This is Hydro-Quebec by the way)

Thanks !

[Hero7777 (Helge Röhrbein)]

I hope that i have understand this, but who can explain me, where i can configure this? I mean maybe the chnage from KWh to kWh.

[OzGav]

If it isn’t defined in anything you have setup then you can use CONFIGURATION>>CUSTOMIZATION

[Fete (a.nonym)]

Let’s see if anyone can understand what I wish for here

In the energy management, I get my data partly from a pulse reader, attached to my electricity meter that the electricity company uses for billing me. And partly from individual devices, connected to smart plugs.

I really like the visualisation with the bars, showing how much has been consumed per hour/day/week etc.

However, I want the bars to concist of what’s been consumed TOTALLY (read from my energy meter) and how much of these bars have been consumed by individual devices. If i add individual devices as “Grid consumption”, sure enough they show up, but they make the totally consumed energy statistics wrong. Since they are already being accounted for in the reading from my electricity meter, they don’t need to be “added to the bar” again as extra consumption. I only want the bar to be broken down. For example "Hour x, total consumption from electricity meter: 10kWh

These 10kWh concists of 1kWh from freezer, 1kWh from cooler, 1kWh from dishwasher, 7kWh from googling HA energy setup".

In this way, it will be way more graphical and intuitive too see what’s been pulling electricity and when.
The consumption of individual devices that are shown under “Sources” does not provide a good graphical visualization and makes it harder to see how much a given device has “contributed” to the total consumption. If the bars could include how much an individual device has “contributed” to it, it would be way better.

I try to add a picture, perhaps some good soul will understand what i’m trying to say

[nickrout]

Don’t add your household devices to grid consumption, add them as individual devices.

[Fete (a.nonym)]

Thank you for your reply. I do have the devices added individually, but their consumption does not show up graphically on the bars. Would be a really helpful functionality if it did. Perhaps it can be solved?

[Michel]

Looks like you added your devices to the grid section instead of the devices section.

[legi (Peter)]

As I’m just a beginner in this area. I wonder if I could add entities which come from my e3dc System. I get the values out of a Modbus integration in my configutation.yaml. Is there a solution for that?

Entities look like sensor.e3dc_xxx

• name: e3dc
  type: tcp
  host: 192.168.1.159
  port: 502
  sensors:
  • name: E3DC Solar Hausdach
    unit_of_measurement: W
    address: 40067
    slave: 1
    device_class: power
  • name: E3DC Batterie
    unit_of_measurement: W
    address: 40069
    slave: 1
    device_class: power
  • name: E3DC Wallbox
    unit_of_measurement: W
    address: 40077
    slave: 1
    device_class: power
  • name: E3DC Hausbezug
    unit_of_measurement: W
    address: 40071
    slave: 1
    device_class: power
  • name: E3DC Netzbezug
    unit_of_measurement: W
    address: 40073
    slave: 1
    device_class: power
  • name: E3DC Solar extern Garage
    address: 40075
    slave: 1
    device_class: power
  • name: E3DC Wallbox solar
    unit_of_measurement: W
    address: 40079
    slave: 1
    device_class: power
  • name: E3DC Autarkie
    data_type: uint
    address: 40081
    slave: 1
  • name: E3DC Batterie Ladung
    data_type: uint
    address: 40082
    slave: 1
  • name: E3DC emergency power state
    data_type: uint
    address: 40083
    slave: 1
  • name: E3DC_emergency_state
    data_type: uint
    address: 40084
    slave: 1
  • name: E3DC_Leistungsmesser-L1
    data_type: uint
    address: 40105
    slave: 1
  • name: E3DC_Leistungsmesser-L2
    data_type: uint
    address: 40106
    slave: 1
  • name: E3DC_Leistungsmesser-L3
    data_type: uint
    address: 40107
    slave: 1

[nickrout]

@fete are you after something like this?

[Insearchofth1ngs]

Any chance we can get the energy dashboard to monitor the consumption of oil use by my oil burning furnace?

[fuziduck (Luke)]

This is what I’m after too.
I have a Shelly EM and I am now using it to track my heat pump electricity consumption.

I would like to split my energy dashboard bar chart between household use and heat pump use.
Other than creating a custom Grafana chart, I can’t see any other way to do this at the minute.

It should be something easy for HA to add in though as you could just select a tick box when adding a meter to grid consumption to ‘not add consumption to overall’
It would also be great if we could show this as a separate icon like the solar production on the little graphic.

[farid]

Hi
I am using a sonoff pow to measure my solar pannel energy generation, but can not see it in the energy drop button, I am using this code, to make it apear, but when I set the state class: I got an error in the Yam file until I errase it. this is my code any Ideas?

sensor:

• platform: template
  sensors:
  today_solar_energy_generation:
  friendly_name: “today_solar_energy_generation”
  device_class: energy
  state_class: total_increasing
  last_reset: ‘1970-01-01T00:00:00.000000+00:00’
  unit_of_measurement: kWh
  value_template: “{{ state_attr(‘switch.sonoff_10011050fb’, ‘consumption’)[0] }}”

[nickrout]

@farid

state_attr(‘switch.sonoff_10011050fb’, ‘consumption’)[0]

Show us what this produces.