Created
December 1, 2023 07:41
-
-
Save AdamTReineke/039f537351c90b38640d70fb94586a94 to your computer and use it in GitHub Desktop.
Compute my time of use pricing based on electric usage captured in Home Assistant.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT | |
month, | |
printf("%.2f", kWh) AS kWh, | |
printf("%.2f", costTimeOfUse) AS costToU, | |
printf("%.2f", CASE | |
WHEN kWh > 800 THEN 800*0.118889 + (kWh-800) * 0.138306 | |
ELSE kWh * 0.118889 | |
END) AS costNow | |
FROM ( | |
SELECT | |
month, | |
SUM(costTimeOfUse) AS costTimeOfUse, | |
SUM(kWh_used) AS kWh | |
FROM ( | |
SELECT | |
isodate, | |
month, | |
kWh_used, | |
costNow, | |
CASE | |
-- Summer evenings | |
WHEN dayOfWeek <= 5 AND hour >= 17 AND hour < 20 AND month >= 4 AND month <= 9 THEN costSummerPeak | |
-- Winter mornings | |
WHEN dayOfWeek <= 5 AND hour >= 7 AND hour < 10 THEN costWinterPeak | |
-- Winter evenings | |
WHEN dayOfWeek <= 5 AND hour >= 17 AND hour < 20 THEN costWinterPeak | |
ELSE costOffPeak | |
END AS costTimeOfUse, | |
CASE | |
-- Summer evenings | |
WHEN dayOfWeek <= 5 AND hour >= 17 AND hour < 20 AND month >= 4 AND month <= 9 THEN 'summer' | |
-- Winter mornings | |
WHEN dayOfWeek <= 5 AND hour >= 7 AND hour < 10 THEN 'winter-am' | |
-- Winter evenings | |
WHEN dayOfWeek <= 5 AND hour >= 17 AND hour < 20 THEN 'winter-pm' | |
ELSE 'offpeak' | |
END AS pricing | |
FROM ( | |
SELECT | |
DATETIME(ROUND(CAST(states.last_updated_ts AS REAL) - 28800), 'unixepoch') AS isodate, | |
CAST(states.state AS REAL) as kWh, | |
CAST(s.state AS REAL) as kWh_prev, | |
ROUND(CAST(states.state AS REAL) - CAST(s.state AS REAL),3) AS kWh_used, | |
ROUND(CAST(states.state AS REAL) - CAST(s.state AS REAL),3)*0.11889 AS costNow, | |
ROUND(CAST(states.state AS REAL) - CAST(s.state AS REAL),3)*0.312776 AS costWinterPeak, | |
ROUND(CAST(states.state AS REAL) - CAST(s.state AS REAL),3)*0.196900 AS costSummerPeak, | |
ROUND(CAST(states.state AS REAL) - CAST(s.state AS REAL),3)*0.063558 AS costOffPeak, | |
CAST(strftime("%H", DATETIME(ROUND(CAST(states.last_updated_ts AS REAL) - 28800), 'unixepoch')) AS INTEGER) AS hour, | |
CAST(strftime("%m", DATETIME(ROUND(CAST(states.last_updated_ts AS REAL) - 28800), 'unixepoch')) AS INTEGER) AS month, | |
CAST(strftime("%w", DATETIME(ROUND(CAST(states.last_updated_ts AS REAL) - 28800), 'unixepoch')) AS INTEGER) + 1 AS dayOfWeek | |
FROM | |
states | |
JOIN | |
states AS s ON states.old_state_id = s.state_id | |
WHERE | |
states.metadata_id = 693 | |
AND CAST(states.state as REAL) > 0 | |
AND kWh_prev > 0 | |
ORDER BY isodate | |
) | |
) | |
WHERE month > 9 | |
GROUP BY month | |
) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment