Compute my time of use pricing based on electric usage captured in Home Assistant.

timeOfUsePricing.sql

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
)