Thank you @boheme61 ! I wasn’t aware of the SQL integration. I used that to create sensors, disabled automatic polling on them, then set up an automation that updates their values and calls the sheets service to add a new row, including the sensor values via a template expression.
Here’s my query, in case anyone else is interested in a long-term statistics example:
SELECT STRFTIME('%Y-%m', hour, 'auto', 'localtime') AS month,
ROUND(SUM(CASE WHEN peak THEN delta ELSE 0 END), 1) AS peak,
ROUND(SUM(CASE WHEN peak THEN 0 ELSE delta END), 1) AS offpeak
FROM (
SELECT start_ts AS hour,
DATETIME(start_ts, 'auto', 'localtime') >= DATETIME(start_ts, 'auto', 'localtime', 'start of day', '+16 hours')
AND DATETIME(start_ts, 'auto', 'localtime') < DATETIME(start_ts, 'auto', 'localtime', 'start of day', '+21 hours') AS peak,
state - LAG(state, 1, 0) OVER(ORDER BY start_ts) AS delta
FROM statistics
LEFT JOIN statistics_meta ON statistics.metadata_id = statistics_meta.id
WHERE statistic_id = 'sensor.powerwall_load_import'
)
WHERE DATETIME(hour, 'auto', 'localtime', 'start of month') = DATETIME('now', 'auto', 'localtime', 'start of month', '-1 months')
GROUP BY month;
This queries the LTS hourly values for my Tesla Powerwall “energy consumed by the house” sensor, dividing them into “peak” (4pm - 9pm) and “off-peak” and then summing those across the month.
The state values in the LTS statistics table are the value of the sensor at the end of the hour, and start_ts is a timestamp representing the beginning of the hour. SQLite seems to require the ‘auto’ modifier to parse these, which was a bit surprising. You usually want to also use ‘localtime’ to interpret the times in your own time zone rather than UTC.
From the statistics table alone you have no idea what these values are; you have to join their metadata_id against the statistics_meta table’s id to select the statistic_name (entity name) that you want to query.
So the inner query uses a window expression to compute the delta between each state and the previous one, and whether it was a peak or off-peak hour. The outer expression then sums up the peak and off-peak values across the entire previous month.
Each SQL sensor can only have one value. For simplicity I just used the same SQL for both, and selected the peak column in one, and offpeak in the other. If I were really concerned about efficiency I could have given each sensor its own query, but these are only getting triggered once a month so it seemed unnecessary.
)