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.