Wow, this is HUUUDGE !!! Thanks! What editor are you using, btw? I am on Linux Mint and trying your queries with "SQLite Studio" and "DB Browser for SQLite" is throwing syntax error (I think because of the rows "lead(timestamp) over (order by timestamp) as next_timestamp,"
From the sqlite3 command line your queries run perfectly, no problems at all. On Sun, Sep 1, 2019 at 11:30 PM Keith Medcalf <kmedc...@dessus.com> wrote: > > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
