This will get you the consumption projection for each day in the table (timestamp in s represents the ENDING period you are interested in and you can modify it to whatever interval you want, and of course the final query gets the result). It works by computing the slope from each timestamp to the next, building the timestamps that you want data for, then computing what the reading would be at that time, and then finally getting the difference from the previous timestamp. It could probably be optimized somewhat, but it works. With the caveat that it assumes the timestamp is in UT1 or a fixed offset from UT1. Since the intervals are defined by s then you could make this a UT1 equivalent table of whatever localtime intervals you need.
and of course you should create in index on power(timestamp, total_kwh) unless you want it to be really very slow with a as ( select timestamp as curr_timestamp, total_kwh as curr_kwh, lead(timestamp) over (order by timestamp) as next_timestamp, lead(total_kwh) over (order by timestamp) as next_kwh from power order by timestamp ), b as ( select curr_timestamp, curr_kwh, (next_kwh - curr_kwh) / (julianday(next_timestamp) - julianday(curr_timestamp)) as rate from a order by curr_timestamp ), s (timestamp) as ( select date(min(timestamp)) || ' 23:59:59' as timestamp from power union all select datetime(timestamp, '+1 day') as timestamp from s where julianday(s.timestamp) < (select max(julianday(timestamp)) from power) ), t (timestamp, total_kwh) as ( select s.timestamp, (select b.curr_kwh + ((julianday(s.timestamp) - julianday(b.curr_timestamp)) * b.rate) from b where julianday(b.curr_timestamp) <= julianday(s.timestamp) order by julianday(b.curr_timestamp) desc) as total_kwh from s order by s.timestamp ), u (timestamp, kwh) as ( select timestamp, total_kwh - lag(total_kwh) over (order by timestamp) as kwh from t order by timestamp ) select date(timestamp), kwh from u where kwh is not null order by 1; eg, for hourly it would be: with a as ( select timestamp as curr_timestamp, total_kwh as curr_kwh, lead(timestamp) over (order by timestamp) as next_timestamp, lead(total_kwh) over (order by timestamp) as next_kwh from power order by timestamp ), b as ( select curr_timestamp, curr_kwh, (next_kwh - curr_kwh) / (julianday(next_timestamp) - julianday(curr_timestamp)) as rate from a order by curr_timestamp ), s (timestamp) as ( select date(min(timestamp)) || ' 00:59:59' as timestamp from power union all select datetime(timestamp, '+1 hour') as timestamp from s where julianday(s.timestamp) < (select max(julianday(timestamp)) from power) ), t (timestamp, total_kwh) as ( select s.timestamp, (select b.curr_kwh + ((julianday(s.timestamp) - julianday(b.curr_timestamp)) * b.rate) from b where julianday(b.curr_timestamp) <= julianday(s.timestamp) order by julianday(b.curr_timestamp) desc) as total_kwh from s order by s.timestamp ), u (timestamp, kwh) as ( select timestamp, total_kwh - lag(total_kwh) over (order by timestamp) as kwh from t order by timestamp ) select substr(timestamp,1,13) || ':00:00' as timestamp, kwh from u where kwh is not null order by 1; -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users