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

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…

Note… a recent breaking change has rendered this method no longer viable. This change: Don't record attributes in sql by gjohansson-ST · Pull Request #120170 · home-assistant/core · GitHub removes saving attributes to the database which this method relies on. Very sad about that @gjohansson :frowning:

No idea what “method” you refer too but make the attribute into it’s own sensor and get it from there instead.
Relying on attributes is not really a go-to solution ever.

I solved it another way. I patched amr2mqtt to send both the net meter read AND increasing values for consumed/returned. The “method” I referred to was not originally mine, but the latest iteration can be found in this post if you are curious. Basically it added an attribute to the SQL sensor so that it would know what the last processed state_id was of the source sensor. That was necessary because the SQL sensor might need to look at multiple states of the source sensor, not just the most recent.