The main sticking point is that my energy meter is a net meter, and home assistant doesn’t seem to know how to handle that. Doing the math, my solar system produced 11.92 kWh and my net meter reports -2.85 kWh, so my home’s usage is 9.07 kWh and I returned 2.85 kWh to the grid. Instead, home assistant thinks my home’s usage is 11.92 kWh, my grid usage is -2.85 kWh (it doesn’t seem to handle negative numbers here very well), and I returned 0 kWh to the grid.
Similar problems were solved on other threads by creating a template sensor to split a single state_class: measurement sensor into two separate sensors, one for consumption and one for return based on the sign of the sensor’s state (positive values were usage, negative values were return, or vice-versa). But my sensor is total, so its value will generally always have the same sign, and that solution won’t really work here.
Looking at the documentation for long-term statistics, the example table has separate sum_increase and sum_decrease columns. I think this is exactly what I want. sum_increase would be grid consumption and sum_decrease would be grid return. I couldn’t figure out how to access those separate columns from a template sensor though. Playing around in the developer tools playground page, I can see that states.statistics is an object, but iterating over it doesn’t reveal any entities. Are those real columns that are being calculated or was it just for the sake of the example? If they’re real, how can I access them in a template sensor?
Is there some way to craft a template sensor that converts a total sensor into a measurement sensor? Once I have a measurement sensor, I can probably apply the same solution from the other posts to get separate sensors for grid consumption and grid return.
Is my current set up something that home assistant should support straight out of the box? All the data is there, it just seems to be doing math a little wrong
It appears that sum_increase and sum_decrease are indeed just for the sake of the example. The long-term statistics are stored in a SQL database, and the schema can be found here (it only has the overall sum column`).
I played around with a couple different ways of doing this:
I tried derivative and integration sensors. I was taking a derivative of my net meter sensor.electric_meter, filtering that derivative into two separate template sensors based on the sign of the derivative, then taking the Riemann sum of those two template sensors. The numbers ended up being pretty far off, so I abandoned this idea (and I don’t have the config I tried to show here).
I tried a couple plain template sensors that did basically what the derivative and integral sensors would do. There were a couple problems if the underlying sensor.electric_meter state was ever unavailable or unknown. And the numbers ended up being a little inaccurate here at times, maybe due to floating point errors
template.yaml:
- trigger:
- platform: state
entity_id: sensor.electric_meter
to: # Skip attribute-only changes.
sensor:
- name: "Electric Meter Consumption"
unique_id: "electric_meter_consumption"
device_class: "energy"
unit_of_measurement: "kWh"
state_class: "total_increasing"
state: >
{% set last = states("sensor.electric_meter_consumption") | float(0) %}
{% 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 %}
{% set from = trigger.from_state.state | float %}
{% set to = trigger.to_state.state | float %}
{% if to - from > 0 %}
{{ last + to - from }}
{% else %}
{{ last }}
{% endif %}
{% endif %}
- name: "Electric Meter Return"
unique_id: "electric_meter_return"
device_class: "energy"
unit_of_measurement: "kWh"
state_class: "total_increasing"
state: >
{% set last = states("sensor.electric_meter_return") | float(0) %}
{% 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 %}
{% set from = trigger.from_state.state | float %}
{% set to = trigger.to_state.state | float %}
{% if to - from > 0 %}
{{ last }}
{% else %}
{{ last + from - to }}
{% endif %}
{% endif %}
Lastly, I tried an SQL sensor. This is maybe a little more resource intensive, but AFAICT there aren’t any inaccuracies in the numbers it reports.
sensor.yaml:
- platform: sql
scan_interval: 300
queries:
- name: electric_meter_consumption_sql
query: >
SELECT SUM(delta) AS state, 'energy' AS device_class, 'total_increasing' AS state_class
FROM (
SELECT state - LAG(state) OVER (ORDER BY state_id) AS delta
FROM states
WHERE entity_id = 'sensor.electric_meter'
AND state != 'unknown'
AND state != 'unavailable')
WHERE delta > 0;
column: state
unit_of_measurement: kWh
- name: electric_meter_return_sql
query: >
SELECT -SUM(delta) AS state, 'energy' AS device_class, 'total_increasing' AS state_class
FROM (
SELECT state - LAG(state) OVER (ORDER BY state_id) AS delta
FROM states
WHERE entity_id = 'sensor.electric_meter'
AND state != 'unknown'
AND state != 'unavailable')
WHERE delta < 0;
column: state
unit_of_measurement: kWh
This still feels like quite a few hoops to jump through to get this work. I might look into seeing if the energy dashboard can support this a little more natively
Hello! I have the same issue with a energy meter which can run both ways to indicate cons/prod. Very smart solution with the sql sensor which seem very accurate, but how do we avoid the issue with HA database autopurge every night 04:12 which messes up the energy tab staples?
Is that sensor one that’s being generated from the sql, or something else?
I think I ran into a similar problem where HomeAssistant eventually purges history from the electric_meter, and I ended up tweaking the sql even more, making it even more complicated and nigh unreadable >.< Instead of just summing the deltas from from the electric_meter, the sql sensor now keeps track of which electric_meter states it has already processed in an attribute, and adds only new electric_meter states to its current value. So for electric_meter_consumption_sql I have
SELECT
MAX(sql_sensor.state) -- All sql_sensor.state values should be the same.
+ SUM( -- SUM the deltas we haven't seen yet.
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
state_id,
state,
IFNULL(json_extract(attributes, '$.last_processed_state_id'), 0) AS last_processed_state_id
FROM states
WHERE 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 - LAG(state) OVER (ORDER BY state_id) AS delta
FROM states
WHERE TRUE
AND entity_id = 'sensor.electric_meter'
AND state != 'unknown'
AND state != 'unavailable'
) AS source_sensor ON (TRUE);
And then electric_meter_return_sql is basically the same with just a few small changes
SELECT
...
+ SUM( -- SUM the deltas we haven't seen yet.
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,
...
FROM (
...
WHERE entity_id = 'sensor.electric_meter_return_sql'
...
) AS sql_sensor LEFT JOIN (
... );
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.”
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
