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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
