I’ve created an SQL to calculate the earnings of a battery. It suits my needs and I think it’s quite flexible to fit other needs. I’ve used this SQL in the SQL integration so I have a sensor displaying the current battery earnings. Now the SQL:
/**
This query calculates a couple of things:
- `cost_with_batt` Current energy cost: only flows through the meter
- `cost_wo_batt` Energy cost without battery: compensate meter usage with battery usage
- `earnings_batt` Difference between the two above: how many your batteries have earned you
- `cost_fixed_pricing` Calculate like if you had a fixed contract.
See the comments in the query to configure it to your wishes
*/
WITH RECURSIVE timeseries(gmt) AS (
-- Configure the from-date below. You could also configure something like `start of month`
SELECT datetime('2025-04-01')
UNION ALL
-- It's not recommend or tested to change the interval, as the statistics table seems to store it on an hourly interval
SELECT datetime(gmt, '+1 hour') FROM timeseries
WHERE datetime(gmt, '+1 hour')<=datetime()
), sensors(statid, type) AS (
-- The first column is the entity id, the second column should not change
-- The pvproduction row is not used
SELECT 'sensor.energy_consumption_tarif_1', 'meter_in'
UNION SELECT 'sensor.energy_consumption_tarif_2', 'meter_in'
UNION SELECT 'sensor.energy_production_tarif_1', 'meter_out'
UNION SELECT 'sensor.energy_production_tarif_2', 'meter_out'
UNION SELECT 'sensor.growatt_dischargeenergytotal', 'batt_out'
UNION SELECT 'sensor.growatt_chargeenergytotal', 'batt_in'
UNION SELECT 'sensor.growatt_pv1energytotal', 'pvproduction'
UNION SELECT 'sensor.tibber_price_details', 'price'
), meta(metadata_id, statid, type) AS (
SELECT statistics_meta.id, statistics_meta.statistic_id, sensors.type
FROM statistics_meta
INNER JOIN sensors ON sensors.statid = statistics_meta.statistic_id
), hourlystates(type, statid, state, ts) AS (
SELECT
type,
meta.statid,
CASE WHEN type = 'price' THEN statistics.mean ELSE statistics.state END,
datetime(strftime('%F %H:00:00', datetime(statistics.start_ts, 'unixepoch'))) AS ts
FROM
statistics
INNER JOIN meta ON meta.metadata_id = statistics.metadata_id
WHERE statistics.start_ts > (SELECT unixepoch(MIN(gmt)) FROM timeseries)
), hourlyvalues(type, statid, ts, val) AS (
SELECT
type,
statid,
ts,
CASE WHEN type = 'price' THEN (last_value(state) OVER w) ELSE last_value(state) OVER w - first_value(state) OVER w END AS val
FROM hourlystates
WINDOW w AS (PARTITION BY type, statid ORDER BY ts ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
), hourlysums(type, ts, val) AS (
SELECT
type,
ts,
SUM(val) AS val
FROM hourlyvalues
GROUP BY type, ts
ORDER BY ts ASC, type
)
SELECT DISTINCT
-- below are the result columns
ROUND(SUM(price.val * (m_in.val - m_out.val)), 2) AS cost_with_batt,
ROUND(SUM(price.val * (m_in.val + batt_out.val - m_out.val - batt_in.val)), 2) AS cost_wo_batt,
ROUND(SUM(price.val * (m_in.val + batt_out.val - m_out.val - batt_in.val)) - SUM(price.val * (m_in.val - m_out.val)), 2) AS earnings_batt,
ROUND(SUM(m_in.val * 0.25 - m_out.val * 0.15), 2) AS cost_fixed_pricing
FROM timeseries
LEFT JOIN hourlysums AS price ON price.type = 'price' AND price.ts = timeseries.gmt
LEFT JOIN hourlysums AS m_in ON m_in.type = 'meter_in' AND m_in.ts = timeseries.gmt
LEFT JOIN hourlysums AS m_out ON m_out.type = 'meter_out' AND m_out.ts = timeseries.gmt
LEFT JOIN hourlysums AS batt_in ON batt_in.type = 'batt_in' AND batt_in.ts = timeseries.gmt
LEFT JOIN hourlysums AS batt_out ON batt_out.type = 'batt_out' AND batt_out.ts = timeseries.gmt
LEFT JOIN hourlysums AS pvprod ON pvprod.type = 'pvproduction' AND pvprod.ts = timeseries.gmt
GROUP BY true
ORDER BY 1 ASC
This is configured as follows:
