How can I configure the energy dashboard to understand my net electric meter?

Hi Jeffrey and Danne, I am in the same boat with rtlamr2mqtt. I was wondering if you had considered trying to address this by creating two MQTT sensors derived from the same MQTT topic: one sensor for positive values and one sensor for negative values?

My meter gives me a reading of the net electric consumption since it was first installed. Currently, the number’s around 1400 kWh. You would still need something keeping track of the deltas between the current reading and the previous reading so you’d know which topic to publish the sum to.

I guess you could do this with a separate MQTT topic, but I’m not sure if it’s any less complex than a template or sql sensor

I see what you mean about keeping track of the previous values - it’s just as messy. It would be handy if the Energy Panel could be programmed to treat a negative consumption delta as “return to grid.”

As i wrote in my last reply it’s (ugly) solved by adding the sql-sensor as utility meter…

I’m glad the sql-sensor is working out, and I may try that. Before I do, in an effort to make something easier for me to understand, I wonder if this approach has any potential (not working- pseudocode):

automations.yaml:

- id: 1
  alias: demux energy
  trigger:
  - platform: mqtt
    topic: readings/XXXXXXXX/meter_reading
  action:
  - service: mqtt.publish
    data_template:
       payload: {{ trigger.from_state.state - trigger.to_state.state | abs }}
       topic: >
        {% if ( trigger.from_state.state < trigger.to_state.state ) %}
            readings/energy-consumed
        {% elif ( trigger.from_state.state > trigger.to_state.state ) %}
            readings/energy-returned
        {% endif %}

Then I can make two energy sensors of ‘state_class: measurement’ based on these two topics. If you think it has potential, could either of you help make the syntax correct?

The MQTT trigger doesn’t give you from_state and to_state templating variables: Automation Trigger Variables - Home Assistant

The state trigger gives you those variables: Automation Trigger Variables - Home Assistant

I had played around with using a state trigger with a template sensor to do this above, but the problem I was running into was unknown and unavailable sensor states messing up the calculations. Basically my electric meter would go from a reading of 5, become unavailable, then have a reading of 11. The state trigger would report two separate events from_state: 5, to_state: unavailable and from_state: unavailable, to_state: 11, and the template sensor would miss the 11 - 5 delta

I’m very interested in this thread. I find it hard to believe that the Energy Panel does not easily support net meters which where I live in California is what we all currently have. I too have a device (emporia vue) that is just giving me net readings. I’ve been working on this for a few days and just stumbled this thread. I’m first going to try the triggers you shared above to understand the issues you were talking about. If the results are only off a little for the day then I like that approach because it is easy for old guys like me to understand–and it is conceptually simpler. While I used SQL in a past job i really hate it and find it had to understand.

Anyway I’m going to follow this discussion. Thanks.

1 Like

So being a newbie am having trouble getting the config file set up. I’ll figure it out but in looking at the script you did above did you consider adding a condition to the trigger so that it only happens if the ‘to state’ is valid?

update: I did get my config files straightened out. :slight_smile: I also added some rounding of the numbers when setting the state. Being a newbie I tried to see how to add a condition on the triggers with the new style template sensor but never figured it out.

I’ll give it a day now to see how close it is to actuals.

If Im happy fine, if not I’ll go down the SQL path.

In the template sensor above I had

        {% if trigger.from_state.state == 'unknown' 
                or trigger.from_state.state == 'unavailable'
                or trigger.to_state.state == 'unknown'
                or trigger.to_state.state == 'unavailable' %}
          {{ last }}
        {% else %}

which was effectively a condition skipping when either state was unknown/unavailable. It probably would have been better off as a condition. Although I’m not sure if template sensors accept conditions like automations do. Looking at the documentation doesn’t show me how to add a condition to a template sensor other than how I did it :confused:

So far, the template sensor approach with added rounding seems to be working OK for me. I submitted a Feature Request about this topic, hoping that the Energy Dashboard devs will consider it.

2 Likes

Not sure how well this would work but maybe you could use a trend binary sensor an automation, and a utility meter with two tariffs (export and import).

This binary sensor should be on when you are importing energy, and off when you are exporting energy:

binary_sensor:
  - platform: trend
    sensors:
      energy_export:
        entity_id: sensor.your_net_energy_sensor
        max_samples: 2
        min_gradient: 0

This utility meter will create two energy sensors, one for import and one for export:

utility_meter:
  grid_energy:
    source: sensor.your_net_energy_sensor
    cycle: daily
    net_consumption: true
    tariffs:
      - import
      - export

This automation switches the utility meter tariffs:

trigger:
  - platform: state
    entity_id: binary_sensor.energy_export
    to:
      - 'on'
      - 'off'
action:
  - service: utility_meter.select_tariff
    data:
      entity_id: utility_meter.grid_energy
      tariff: "{{ 'export' if trigger.to_state.state == 'on' else 'import' }}"

My attempt to solve this problem used the statistics sensor to difference the samples.

sensor:
  - platform: statistics    #seems to miss some points so systematically underreports
    name: "Electricity net meter delta"
    entity_id: sensor.electricity_net_meter
    state_characteristic: change
    sampling_size: 2

and I have a binary sensor to detect when it goes negative

binary_sensor:
  - platform: threshold
    entity_id: sensor.electricity_net_meter_delta
    lower: 0
    name: electricity grid return

I split the signal from the rtlamr2mqtt like this:

  - sensor:
      - name: "electricity grid delta consumption"
        unit_of_measurement: "kWh"
        state: >
          {{ [states('sensor.electricity_net_meter_delta')|float, 0 ]|max }}
        device_class: energy
        state_class: total     
  - sensor:
      - name: "electricity grid delta return"
        unit_of_measurement: "kWh"
        state: >
          {{ [states('sensor.electricity_net_meter_delta')|float, 0 ]|min |abs }}
        device_class: energy
        state_class: total

I define the utility meters like this, using the delta values option

utility_meter:
   daily_electricity_grid_consumption:   
        source: sensor.electricity_grid_delta_consumption
        cycle: daily
        net_consumption: false
        delta_values: true
    daily_electricity_grid_return:   
        source: sensor.electricity_grid_delta_return
        cycle: daily
        net_consumption: false
        delta_values: true

This seemed to work ok, but on closer inspection, the statistics sensor seems to lose samples, and so slowly diverges from the underlying signal. You can see that it misses the increase at 10:42 and 10:57 etc.

I would appreciate any insights on why some of the data points may be missed.

Thanks

I have the same setup as you, i am having exactly the same issue.
It skips those times because the value does not change.
Because of this the utility meter thinks it is an unchanged value and does not update.
Have you solved this issue?

I have this same issue as well. Wondering if others have gleaned a solution?

I wanted to use @JeffreyFalgout’s SQL sensor solution, but I use PostgreSQL as my recorder database instead of MySQL or SQLite, so I had to do some tweaking to the queries. Below are my revised queries for PostgreSQL, plus the updated SQL sensor entities in YAML. I’m pleased to report that everything is working perfectly, and I’m grateful that most of the work was already done by @JeffreyFalgout!

Note that the SQL sensor syntax seems to have changed a bit in the interim.

Production Sensor Query

SELECT
    MAX(sql_sensor.state)
    + SUM(
        CASE
        source_sensor.state_id > sql_sensor.last_processed_state_id
        AND source_sensor.delta > 0
            WHEN TRUE THEN source_sensor.delta
            ELSE 0
        END
    ) AS state,
    MAX(source_sensor.state_id) AS last_processed_state_id,
    'energy' AS device_class,
    'total_increasing' AS state_class
FROM (
    SELECT
        states.state_id AS state_id,
        CASE
            WHEN states.state = 'unknown'
                THEN 0
            ELSE states.state::numeric
        END AS state,
        COALESCE(
            (state_attributes.shared_attrs::json ->> 'last_processed_state_id'
            )::numeric, 0
        ) AS last_processed_state_id
    FROM states
    LEFT JOIN
        state_attributes
        ON states.attributes_id = state_attributes.attributes_id
    WHERE states.entity_id = 'sensor.electric_meter_consumption_sql'
    UNION ALL
    SELECT
        0 AS state_id,
        0 AS state,
        0 AS last_processed_state_id
    ORDER BY state_id DESC
    LIMIT 1
) AS sql_sensor LEFT JOIN (
    SELECT
        state_id,
        state::numeric - LAG(state::numeric) OVER (ORDER BY state_id)
        AS delta
    FROM states
    WHERE
        TRUE
        AND entity_id = 'sensor.meter_electric_new'
        AND state != 'unknown'
        AND state != 'unavailable'
) AS source_sensor ON (TRUE);

Production Sensor YAML

- name: Electric Meter Consumption SQL
  db_url: !secret postgres_db_url
  query: >
    [insert query above]
  column: state
  unit_of_measurement: kWh

Return Sensor Query

SELECT
    MAX(sql_sensor.state)
    + SUM(
        CASE
        source_sensor.state_id > sql_sensor.last_processed_state_id
        AND source_sensor.delta < 0
            WHEN TRUE THEN -source_sensor.delta
            ELSE 0
        END
    ) AS state,
    MAX(source_sensor.state_id) AS last_processed_state_id,
    'energy' AS device_class,
    'total_increasing' AS state_class
FROM (
    SELECT
        states.state_id AS state_id,
        CASE
            WHEN states.state = 'unknown'
                THEN 0
            ELSE states.state::numeric
        END
        AS state,
        COALESCE(
            (
                state_attributes.shared_attrs::json
                ->> 'last_processed_state_id'
            )::numeric,
            0
        ) AS last_processed_state_id
    FROM states
    LEFT JOIN
        state_attributes
        ON states.attributes_id = state_attributes.attributes_id
    WHERE states.entity_id = 'sensor.electric_meter_return_sql'
    UNION ALL
    SELECT
        0 AS state_id,
        0 AS state,
        0 AS last_processed_state_id
    ORDER BY state_id DESC
    LIMIT 1
) AS sql_sensor LEFT JOIN (
    SELECT
        state_id,
        state::numeric - LAG(state::numeric) OVER (ORDER BY state_id)
        AS delta
    FROM states
    WHERE
        TRUE
        AND entity_id = 'sensor.meter_electric_new'
        AND state != 'unknown'
        AND state != 'unavailable'
) AS source_sensor ON (TRUE);

Return Sensor YAML

- name: Electric Meter Return SQL
  db_url: !secret postgres_db_url
  query: >
    [insert query above]
  column: state
  unit_of_measurement: kWh

Here’s updated consumption SQL for 2023.04:

SELECT
  MAX(sql_sensor.state)  -- All sql_sensor.state values should be the same.
    + SUM(  -- SUM the deltas we haven't seen yet.
        IIF( 
          source_sensor.state_id > sql_sensor.last_processed_state_id
            AND source_sensor.delta > 0,
          source_sensor.delta,
          0)) AS state,
  COALESCE(MAX(source_sensor.state_id), sql_sensor.last_processed_state_id) AS last_processed_state_id,
  'energy' AS device_class,
  'total_increasing' AS state_class
FROM (
  -- Determine the most recent state for sensor.electric_meter_consumption_sql
  SELECT
    state.state_id,
    state.state,
    json_extract(attrs.shared_attrs, '$.last_processed_state_id') AS last_processed_state_id
  FROM states AS state
    LEFT JOIN states_meta AS meta
    USING (metadata_id)
    LEFT JOIN state_attributes AS attrs
    USING (attributes_id)
  WHERE TRUE
    AND state.state != 'unknown'
    AND meta.entity_id = 'sensor.electric_meter_consumption_sql'
  UNION ALL
  SELECT
    0 AS state_id,
    0 AS state,
    0 AS last_processed_state_id
  ORDER BY state_id DESC
  LIMIT 1
) AS sql_sensor, (
  -- Calculate deltas for sensor.electric_meter
  SELECT
    state.state_id,
    state.state - LAG(state.state) OVER (ORDER BY state.state_id) AS delta
  FROM states AS state
    LEFT JOIN states_meta AS meta
    USING (metadata_id)
  WHERE TRUE
    AND state.state != 'unknown'
    AND state.state != 'unavailable'
    AND meta.entity_id = 'sensor.electric_meter'
) AS source_sensor;

I’ve been trying to get this going as well and have some success. I updated the SQL to handle the entity_id no longer being in the states table and for the most part everything seems to be working. However I am getting these weird jumps in data on occasion.


The source sensor data does not have a matching jump, but since my account is new I can’t attach a screen shot of that. :roll_eyes:

It is curious, I notice that it relies on the state_attributes table for previous state, and at the time of the odd jump, there appears to be a “corrupt” entry in that table missing the ‘last_processed_state_id’:

{"unit_of_measurement":"kWh","friendly_name":"New Electric Meter Consumption"}

My current config:

- name: electric_meter_consumption_sql
  query: >
    SELECT
        MAX(sql_sensor.state)
        + SUM(
            CASE
            source_sensor.state_id > sql_sensor.last_processed_state_id
            AND source_sensor.delta > 0
                WHEN TRUE THEN source_sensor.delta
                ELSE 0
            END
        ) AS state,
        MAX(source_sensor.state_id) AS last_processed_state_id,
        'energy' AS device_class,
        'total_increasing' AS state_class
    FROM (
        SELECT
            states.state_id AS state_id,
            CASE
                WHEN states.state = 'unknown'
                    THEN 0
                ELSE states.state::numeric
            END AS state,
            COALESCE(
                (state_attributes.shared_attrs::json ->> 'last_processed_state_id'
                )::numeric, 0
            ) AS last_processed_state_id
        FROM states
        LEFT JOIN
            state_attributes
            ON states.attributes_id = state_attributes.attributes_id
        WHERE states.entity_id = 'sensor.electric_meter_consumption_sql'
        UNION ALL
        SELECT
            0 AS state_id,
            0 AS state,
            0 AS last_processed_state_id
        ORDER BY state_id DESC
        LIMIT 1
    ) AS sql_sensor LEFT JOIN (
        SELECT
            state_id,
            state::numeric - LAG(state::numeric) OVER (ORDER BY state_id)
            AS delta
        FROM states
        LEFT JOIN 
            states_meta AS meta
            ON states.metadata_id = meta.metadata_id
        WHERE
            TRUE
            AND meta.entity_id = 'sensor.new_electric_meter_consumption'
            AND state != 'unknown'
            AND state != 'unavailable'
    ) AS source_sensor ON (TRUE);
  column: state
  unit_of_measurement: kWh
- name: electric_meter_return_sql
  query: >
    SELECT
        MAX(sql_sensor.state)
        + SUM(
            CASE
            source_sensor.state_id > sql_sensor.last_processed_state_id
            AND source_sensor.delta < 0
                WHEN TRUE THEN -source_sensor.delta
                ELSE 0
            END
        ) AS state,
        MAX(source_sensor.state_id) AS last_processed_state_id,
        'energy' AS device_class,
        'total_increasing' AS state_class
    FROM (
        SELECT
            states.state_id AS state_id,
            CASE
                WHEN states.state = 'unknown'
                    THEN 0
                ELSE states.state::numeric
            END
            AS state,
            COALESCE(
                (
                    state_attributes.shared_attrs::json
                    ->> 'last_processed_state_id'
                )::numeric,
                0
            ) AS last_processed_state_id
        FROM states
        LEFT JOIN
            state_attributes
            ON states.attributes_id = state_attributes.attributes_id
        WHERE states.entity_id = 'sensor.electric_meter_return_sql'
        UNION ALL
        SELECT
            0 AS state_id,
            0 AS state,
            0 AS last_processed_state_id
        ORDER BY state_id DESC
        LIMIT 1
    ) AS sql_sensor LEFT JOIN (
        SELECT
            state_id,
            state::numeric - LAG(state::numeric) OVER (ORDER BY state_id)
            AS delta
        FROM states
        LEFT JOIN 
            states_meta AS meta
            ON states.metadata_id = meta.metadata_id

        WHERE
            TRUE
            AND meta.entity_id = 'sensor.new_electric_meter_consumption'
            AND state != 'unknown'
            AND state != 'unavailable'
    ) AS source_sensor ON (TRUE);
  column: state
  unit_of_measurement: kWh

I found at least one issue, it remains to be seen if it fixes it. I had missed one of the entity_id update locations:

- name: electric_meter_consumption_sql
  query: >
    SELECT
        MAX(sql_sensor.state)
        + SUM(
            CASE
            source_sensor.state_id > sql_sensor.last_processed_state_id
            AND source_sensor.delta > 0
                WHEN TRUE THEN source_sensor.delta
                ELSE 0
            END
        ) AS state,
        MAX(source_sensor.state_id) AS last_processed_state_id,
        'energy' AS device_class,
        'total_increasing' AS state_class
    FROM (
        SELECT
            states.state_id AS state_id,
            CASE
                WHEN states.state = 'unknown'
                    THEN 0
                ELSE states.state::numeric
            END AS state,
            COALESCE(
                (state_attributes.shared_attrs::json ->> 'last_processed_state_id'
                )::numeric, 0
            ) AS last_processed_state_id
        FROM states
        LEFT JOIN
            state_attributes
            ON states.attributes_id = state_attributes.attributes_id
        LEFT JOIN 
            states_meta AS meta
            ON states.metadata_id = meta.metadata_id
        WHERE meta.entity_id = 'sensor.electric_meter_consumption_sql'
        UNION ALL
        SELECT
            0 AS state_id,
            0 AS state,
            0 AS last_processed_state_id
        ORDER BY state_id DESC
        LIMIT 1
    ) AS sql_sensor LEFT JOIN (
        SELECT
            state_id,
            state::numeric - LAG(state::numeric) OVER (ORDER BY state_id)
            AS delta
        FROM states
        LEFT JOIN 
            states_meta AS meta
            ON states.metadata_id = meta.metadata_id
        WHERE
            TRUE
            AND meta.entity_id = 'sensor.new_electric_meter_consumption'
            AND state != 'unknown'
            AND state != 'unavailable'
    ) AS source_sensor ON (TRUE);
  column: state
  unit_of_measurement: kWh
- name: electric_meter_return_sql
  query: >
    SELECT
        MAX(sql_sensor.state)
        + SUM(
            CASE
            source_sensor.state_id > sql_sensor.last_processed_state_id
            AND source_sensor.delta < 0
                WHEN TRUE THEN -source_sensor.delta
                ELSE 0
            END
        ) AS state,
        MAX(source_sensor.state_id) AS last_processed_state_id,
        'energy' AS device_class,
        'total_increasing' AS state_class
    FROM (
        SELECT
            states.state_id AS state_id,
            CASE
                WHEN states.state = 'unknown'
                    THEN 0
                ELSE states.state::numeric
            END
            AS state,
            COALESCE(
                (
                    state_attributes.shared_attrs::json
                    ->> 'last_processed_state_id'
                )::numeric,
                0
            ) AS last_processed_state_id
        FROM states
        LEFT JOIN
            state_attributes
            ON states.attributes_id = state_attributes.attributes_id
        LEFT JOIN 
            states_meta AS meta
            ON states.metadata_id = meta.metadata_id
        WHERE meta.entity_id = 'sensor.electric_meter_return_sql'
        UNION ALL
        SELECT
            0 AS state_id,
            0 AS state,
            0 AS last_processed_state_id
        ORDER BY state_id DESC
        LIMIT 1
    ) AS sql_sensor LEFT JOIN (
        SELECT
            state_id,
            state::numeric - LAG(state::numeric) OVER (ORDER BY state_id)
            AS delta
        FROM states
        LEFT JOIN 
            states_meta AS meta
            ON states.metadata_id = meta.metadata_id

        WHERE
            TRUE
            AND meta.entity_id = 'sensor.new_electric_meter_consumption'
            AND state != 'unknown'
            AND state != 'unavailable'
    ) AS source_sensor ON (TRUE);
  column: state
  unit_of_measurement: kWh

The way I fixed this is a little more elegant imho, so I’m going to leave this here for posterity.
The utility meters can keep track of increasing values. So if you use one meter that tracks increasing values and another that tracks increasing values of and inverted sensor, you now have a split meter.

template:
  sensor:
    # We need the inverted Grid Energy to feed the Grid Production meter
    - name: "Grid Energy Inverted"
      unique_id: grid_energy_inverted
      state_class: total
      device_class: energy
      unit_of_measurement: "kWh"
      state: "{{ states('sensor.grid_energy') | float * -1 }}"

utility_meter:
  grid_energy_consumed:
    name: "Grid Energy Consumed"
    unique_id: grid_energy_consumed
    source: sensor.grid_energy
    delta_values: false
    net_consumption: false
    periodically_resetting: false
  grid_energy_produced:
    name: "Grid Energy Produced"
    unique_id: grid_energy_produced
    source: sensor.grid_energy_inverted
    delta_values: false
    net_consumption: false
    periodically_resetting: false

Not sure if this is the right thread for my question, but it is about netting done by electric company. As for now, they are recording the net amount of electric consumption or production, so you can only have one or another, not both at the same time. Here’s an example:

image
So, in this case, I would have only consumption from 10-11, then only production from 11-12 and then only consumption from 12-13. The smaller of those two is always reduced from the bigger number. So, for example from 12-13 the blue bar is 0,17kWh and purple bar is -0,12, which my electric company counts as I have consumed 0,05 kWh and produced nothing.

I guess I would like to see the “real” import from grid and output to grid, but maybe I would like another view that would do the “netting” for me… Or how do others have solved this? Also, starting from 2025, they are switching from hourly netting to new system where they are doing that in every 15 minutes. Maybe we get some sort of update to energy dashboard then as I guess that’s coming in many countries…