Time of Use

instructions.md

Import HVAC runtime data

sqlite-utils insert energy.db energy report-310102929379-2023-05-22-to-2023-06-22.csv --csv

Import smart meter usage data

sqlite-utils insert energy.db intervals IntervalData.csv --csv

Query

WITH EnergyTOU AS (
	SELECT
		*,
		CASE
			WHEN [Time] BETWEEN '08:00:00' AND '20:00:00' THEN 'Day'
			ELSE 'Night'
		END TimeOfUse
	FROM energy 
), IntervalsFixed AS (
	SELECT
		*,
		printf('%s-%s-%s', substr(USAGE_DATE, 7, 4), substr(USAGE_DATE, 1, 2), substr(USAGE_DATE, 4, 2)) Date,
		printf('%s:00', USAGE_START_TIME) Time
	FROM intervals
), UsageByTime AS (
	SELECT
		TimeOfUse,
		SUM([Cool Stage 1 (sec)]) Runtime,
		SUM([USAGE_KWH]) Usage
	FROM EnergyTOU
	JOIN IntervalsFixed ON
		IntervalsFixed.Date = EnergyTOU.Date
	AND IntervalsFixed.Time = EnergyTOU.Time
	GROUP BY TimeOfUse
), UsageRates AS (
	SELECT
		*,
		CASE [TimeOfUse]
			WHEN 'Day' THEN 7.3886
			WHEN 'Night' THEN 5.3886
		END Rate
	FROM UsageByTime
), UsageCosts AS (
	SELECT
		*,
		(Usage * Rate * 0.01) Cost
	FROM UsageRates
), TotalCosts AS (	
	SELECT
		*
	FROM UsageCosts
	UNION ALL
	SELECT
		'Total Cost',
		SUM(Runtime),
		SUM(Usage),
		AVG(Rate),
		SUM(Cost)
	FROM UsageCosts
)

SELECT
	TimeOfUse,
	printf('%,d sec', Runtime) Runtime,
	printf('%,.3f kWh', Usage) Usage,
	printf('%.4f ¢/kWh', Rate) Rate,
	printf('$%,.2f', Cost) Cost
FROM TotalCosts

Example output

TimeOfUse	Runtime	Usage	Rate	Cost
Day	136,125 sec	663.255 kWh	7.3886 ¢/kWh	$49.01
Night	191,235 sec	690.592 kWh	5.3886 ¢/kWh	$37.21
Total Cost	327,360 sec	1353.847 kWh	6.3886 ¢/kWh	$86.22