this is what I came up with, I don’t have enough data yet to see if it is working.
sql:
# Current total energy returned to grid
- name: "Energy Returned to Grid Total"
query: >
SELECT
s.sum as total_kwh
FROM statistics s
JOIN statistics_meta sm ON s.metadata_id = sm.id
WHERE sm.statistic_id = 'sensor.power_meter_[YOUR_METER_ID]_kwh_to_grid'
ORDER BY s.start_ts DESC
LIMIT 1;
column: "total_kwh"
unit_of_measurement: "kWh"
device_class: "energy"
state_class: "total_increasing"
# Current power being returned to grid
- name: "Power Returned to Grid"
query: >
SELECT
s.state as current_power
FROM statistics s
JOIN statistics_meta sm ON s.metadata_id = sm.id
WHERE sm.statistic_id = 'sensor.power_meter_[YOUR_METER_ID]_kwh_to_grid'
ORDER BY s.start_ts DESC
LIMIT 1;
column: "current_power"
unit_of_measurement: "kW"
device_class: "power"
state_class: "measurement"
# Today's energy returned to grid
- name: "Energy Returned to Grid Today"
query: >
SELECT
COALESCE(MAX(s.sum) - MIN(s.sum), 0) as daily_kwh
FROM statistics s
JOIN statistics_meta sm ON s.metadata_id = sm.id
WHERE sm.statistic_id = 'sensor.power_meter_[YOUR_METER_ID]_kwh_to_grid'
AND s.start_ts >= strftime('%s', 'now', 'start of day')
AND s.start_ts < strftime('%s', 'now', '+1 day', 'start of day');
column: "daily_kwh"
unit_of_measurement: "kWh"
device_class: "energy"
state_class: "total"
# This week's energy returned to grid
- name: "Energy Returned to Grid This Week"
query: >
SELECT
COALESCE(MAX(s.sum) - MIN(s.sum), 0) as weekly_kwh
FROM statistics s
JOIN statistics_meta sm ON s.metadata_id = sm.id
WHERE sm.statistic_id = 'sensor.power_meter_[YOUR_METER_ID]_kwh_to_grid'
AND s.start_ts >= strftime('%s', 'now', 'weekday 0', '-7 days')
AND s.start_ts < strftime('%s', 'now', 'weekday 0');
column: "weekly_kwh"
unit_of_measurement: "kWh"
device_class: "energy"
state_class: "total"
# This month's energy returned to grid
- name: "Energy Returned to Grid This Month"
query: >
SELECT
COALESCE(MAX(s.sum) - MIN(s.sum), 0) as monthly_kwh
FROM statistics s
JOIN statistics_meta sm ON s.metadata_id = sm.id
WHERE sm.statistic_id = 'sensor.power_meter_[YOUR_METER_ID]_kwh_to_grid'
AND s.start_ts >= strftime('%s', 'now', 'start of month')
AND s.start_ts < strftime('%s', 'now', '+1 month', 'start of month');
column: "monthly_kwh"
unit_of_measurement: "kWh"
device_class: "energy"
state_class: "total"