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):
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?
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.
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. 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.
{% 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
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.
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:
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
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 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 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
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.
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.
