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.
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 an 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:
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…
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.
