Dear Eon - Measuring Deltas Correctly for Smart Energy Meters

This is an awesome question!

The TLDR is you should create a continuous aggregate with just the counter_agg part and then apply the the proper accessors in a query to it. You can get all of the information you want from a counter_agg:

CREATE MATERIALIZED VIEW daily_meter
WITH (timescaledb.continuous) 
AS 
SELECT 
	meter_id, 
    time_bucket('1 day', ts) as bucket,   
    counter_agg(ts, total_kwh)
FROM meter_readings
GROUP BY 1, 2;

Once you’ve done that, (also probably created a continuous aggregate policy which I’ll go into below) if you want the daily usage you can query the continuous aggregate like so:

SELECT 
	bucket,
	meter_id,
	first_val(counter_agg), 
	last_val(counter_agg),
	toolkit_experimental.interpolated_delta(
		counter_agg, 
		bucket, 
		'1 day'::interval, 
		lag(counter_agg) OVER ordered_meter, 
		lead(counter_agg) OVER ordered_meter)
FROM daily_meter
WINDOW ordered_meter AS (PARTITION BY meter_id ORDER BY bucket)
ORDER BY meter_id, bucket;

Monthly usage will be similar, except you can actually create a monthly rollup in a separate hierarchical continuous aggregate on top of the first, using the rollup function for counter_agg. And then you can write a similar query as above.

CREATE MATERIALIZED VIEW daily_meter
WITH (timescaledb.continuous) 
AS 
SELECT 
	meter_id, 
    time_bucket('1 month', ts) as bucket,   
    rollup(counter_agg)
FROM meter_readings
GROUP BY 1, 2;

(Or if you want more dynamic months you can use rollup over the correct subsets of days in a query, feel free to ask for clarification on this).

I’m planning to write a longer answer looking at how all this works, but I might make it into a separate topic so it doesn’t get too long, will update this with a link if I do.