Re: [sqlite] select for power-meter accumulated total readings

[Petr Jakeš]

On Sun, Oct 20, 2019 at 8:23 PM Petr Jakeš  wrote:

>
>
> On Sun, Oct 20, 2019 at 4:36 PM Keith Medcalf  wrote:
>
>>
>> On Sunday, 20 October, 2019 06:58, Petr Jakeš 
>> wrote:
>>
>> >On Sun, Oct 20, 2019 at 2:53 AM Keith Medcalf 
>> wrote:
>>
>> >> On Saturday, 19 October, 2019 18:26, Petr Jakeš <
>> petr.jakes@gmail.com> wrote:
>>
>> >>> After long time I have set up development environment properly and I
>> >>> am able to start to study your queries.
>>
>> >>> I am lost. I don't either understand the first bunch of subqueries...
>> >>> (What is returned in the "ratetoprior"? I have been pulling my hair
>> >>> over 3 hours trying to figure it out ... no clue what it means.
>>
>> >> RateToPrior is the linear slope of the line which joins the current
>> >> value to the prior value.  Once you know the slope of that line, then
>> for any
>> >> point in time you merely need to find the next reading after that point
>> >> in time and use the slope to calculate/interpolate what the value would
>> >> have been at the time you are interested in (assuming that the value
>> change
>> >> is linear).
>>
>> >> Does this help?
>>
>> > If I understand it properly:
>>
>> >   - timestamps are considered as x values, readings as y values
>> >   - *pwr* statement calculates slope of the line
>> >    (delta y / delta x) between all
>> >   adjacent readings
>> >   - *periods* statement calculates (fills in) the dates according to the
>> >   requested granity
>> >   - *readings* statement calculates readings from the next reading (next
>> >   higher timestamp to the periods datestamp)
>> >   - *used* statement calculates consumption (lag between two adjacent
>> >   rows)
>>
>> > My confusion was I didn't expect all values are calculated, not just
>> > returned directly from the database as I was almost doing.
>> > Your approach is GENIAL! Thank you.
>>
>> You got it exactly.  It really does not matter if one of your requested
>> points (x) happens to match an actual sample, as the calculated result from
>> the next sample (y) should match that value because it is interpolating
>> backwards -- it is also easier to always calculate the appropriate y for
>> the given x, than it is to figure out where the x samples are and fill in
>> the blanks.
>>
>> > Do you mind if I will mention you/your solution on my blog (in the time
>> > when I write post about power/water metering)?
>>
>> Not at all.
>>
>> > What about if I want 1 hour granity? (to plot a graph of daily
>> > consumption for example)
>>
>> This should work exactly the same way.  You just need to generate the
>> *periods* so that they match the x values for which you want to calculate
>> readings.  Since the *used* table reflects the lag between two adjacent
>> rows, you need your *periods* to include the last hour of the previous day
>> and perhaps the first hour of the next day (so 26 points, as it were), and
>> then filter it afterwards when you plot your graph.  Note that this will be
>> a "snapshot" based graph, not an "average" based graph.
>>
>> This scheme is generally how Process Historians work for continuous
>> control.  Once each minute (the exact offset into the minute does not
>> really matter nor does it need to be the same, it is just frequent
>> readings) the instantaneous value is collected and stored together with the
>> computed slope to the previous value.  From this you can interpolate what
>> the instantaneous value was at exactly each minute boundary.  This gives
>> you a series of polygons which are basically estimates of the shape of the
>> curve.  The historian will generate the one minute "snapshot" values and
>> average those to get each hourly average.  It can also compute the
>> "confidence" in the accuracy of that value based on the number of actual
>> samples in the interval (vs the number there ought to be).  Industry
>> standard values are called "raw" which is the actual recorded data with no
>> interpolation, "snapshot" (point in time interpolated values), one minute
>> average (which is different from the snapshot since the value usually
>> changed sometime during the minute and the average is computed assuming
>> that the prior value was in effect up until the change, and the new value
>> afterwards, ie, that the readings are discrete -- or it may average the
>> start of minute and the end of minute snapshots depending on
>> configuration), six minute average on every 6 minute boundary (the average
>> of the 6 one minute snapshot values (at minute offset 0, 6, 12 ... in the
>> hour), and hourly average on every hour (the average of the 60 one minute
>> snapshot values).  Intervals less than a day the y (timestamp) is the at
>> the end of the interval, and for intervals greater than a day the y
>> (timestamp) is the beginning of the interval.  Sometimes the sample
>> frequency is increased beyond 1 minute, in which case the calculations are
>> all based on that sample frequency giving you a bette

Re: [sqlite] select for power-meter accumulated total readings

[Petr Jakeš]

On Sun, Oct 20, 2019 at 4:36 PM Keith Medcalf  wrote:

>
> On Sunday, 20 October, 2019 06:58, Petr Jakeš 
> wrote:
>
> >On Sun, Oct 20, 2019 at 2:53 AM Keith Medcalf 
> wrote:
>
> >> On Saturday, 19 October, 2019 18:26, Petr Jakeš <
> petr.jakes@gmail.com> wrote:
>
> >>> After long time I have set up development environment properly and I
> >>> am able to start to study your queries.
>
> >>> I am lost. I don't either understand the first bunch of subqueries...
> >>> (What is returned in the "ratetoprior"? I have been pulling my hair
> >>> over 3 hours trying to figure it out ... no clue what it means.
>
> >> RateToPrior is the linear slope of the line which joins the current
> >> value to the prior value.  Once you know the slope of that line, then
> for any
> >> point in time you merely need to find the next reading after that point
> >> in time and use the slope to calculate/interpolate what the value would
> >> have been at the time you are interested in (assuming that the value
> change
> >> is linear).
>
> >> Does this help?
>
> > If I understand it properly:
>
> >   - timestamps are considered as x values, readings as y values
> >   - *pwr* statement calculates slope of the line
> >    (delta y / delta x) between all
> >   adjacent readings
> >   - *periods* statement calculates (fills in) the dates according to the
> >   requested granity
> >   - *readings* statement calculates readings from the next reading (next
> >   higher timestamp to the periods datestamp)
> >   - *used* statement calculates consumption (lag between two adjacent
> >   rows)
>
> > My confusion was I didn't expect all values are calculated, not just
> > returned directly from the database as I was almost doing.
> > Your approach is GENIAL! Thank you.
>
> You got it exactly.  It really does not matter if one of your requested
> points (x) happens to match an actual sample, as the calculated result from
> the next sample (y) should match that value because it is interpolating
> backwards -- it is also easier to always calculate the appropriate y for
> the given x, than it is to figure out where the x samples are and fill in
> the blanks.
>
> > Do you mind if I will mention you/your solution on my blog (in the time
> > when I write post about power/water metering)?
>
> Not at all.
>
> > What about if I want 1 hour granity? (to plot a graph of daily
> > consumption for example)
>
> This should work exactly the same way.  You just need to generate the
> *periods* so that they match the x values for which you want to calculate
> readings.  Since the *used* table reflects the lag between two adjacent
> rows, you need your *periods* to include the last hour of the previous day
> and perhaps the first hour of the next day (so 26 points, as it were), and
> then filter it afterwards when you plot your graph.  Note that this will be
> a "snapshot" based graph, not an "average" based graph.
>
> This scheme is generally how Process Historians work for continuous
> control.  Once each minute (the exact offset into the minute does not
> really matter nor does it need to be the same, it is just frequent
> readings) the instantaneous value is collected and stored together with the
> computed slope to the previous value.  From this you can interpolate what
> the instantaneous value was at exactly each minute boundary.  This gives
> you a series of polygons which are basically estimates of the shape of the
> curve.  The historian will generate the one minute "snapshot" values and
> average those to get each hourly average.  It can also compute the
> "confidence" in the accuracy of that value based on the number of actual
> samples in the interval (vs the number there ought to be).  Industry
> standard values are called "raw" which is the actual recorded data with no
> interpolation, "snapshot" (point in time interpolated values), one minute
> average (which is different from the snapshot since the value usually
> changed sometime during the minute and the average is computed assuming
> that the prior value was in effect up until the change, and the new value
> afterwards, ie, that the readings are discrete -- or it may average the
> start of minute and the end of minute snapshots depending on
> configuration), six minute average on every 6 minute boundary (the average
> of the 6 one minute snapshot values (at minute offset 0, 6, 12 ... in the
> hour), and hourly average on every hour (the average of the 60 one minute
> snapshot values).  Intervals less than a day the y (timestamp) is the at
> the end of the interval, and for intervals greater than a day the y
> (timestamp) is the beginning of the interval.  Sometimes the sample
> frequency is increased beyond 1 minute, in which case the calculations are
> all based on that sample frequency giving you a better approximation of the
> curve.  (It is all really nothing more than just using Eulers method to
> collect samples which will allow an integral to be app

Re: [sqlite] select for power-meter accumulated total readings

[Keith Medcalf]

On Sunday, 20 October, 2019 06:58, Petr Jakeš  wrote:

>On Sun, Oct 20, 2019 at 2:53 AM Keith Medcalf  wrote:

>> On Saturday, 19 October, 2019 18:26, Petr Jakeš  
>> wrote:

>>> After long time I have set up development environment properly and I
>>> am able to start to study your queries.

>>> I am lost. I don't either understand the first bunch of subqueries...
>>> (What is returned in the "ratetoprior"? I have been pulling my hair
>>> over 3 hours trying to figure it out ... no clue what it means.

>> RateToPrior is the linear slope of the line which joins the current
>> value to the prior value.  Once you know the slope of that line, then for any
>> point in time you merely need to find the next reading after that point
>> in time and use the slope to calculate/interpolate what the value would
>> have been at the time you are interested in (assuming that the value change
>> is linear).

>> Does this help?

> If I understand it properly:

>   - timestamps are considered as x values, readings as y values
>   - *pwr* statement calculates slope of the line
>    (delta y / delta x) between all
>   adjacent readings
>   - *periods* statement calculates (fills in) the dates according to the
>   requested granity
>   - *readings* statement calculates readings from the next reading (next
>   higher timestamp to the periods datestamp)
>   - *used* statement calculates consumption (lag between two adjacent
>   rows)

> My confusion was I didn't expect all values are calculated, not just
> returned directly from the database as I was almost doing.
> Your approach is GENIAL! Thank you.

You got it exactly.  It really does not matter if one of your requested points 
(x) happens to match an actual sample, as the calculated result from the next 
sample (y) should match that value because it is interpolating backwards -- it 
is also easier to always calculate the appropriate y for the given x, than it 
is to figure out where the x samples are and fill in the blanks.

> Do you mind if I will mention you/your solution on my blog (in the time
> when I write post about power/water metering)?

Not at all.

> What about if I want 1 hour granity? (to plot a graph of daily
> consumption for example)

This should work exactly the same way.  You just need to generate the *periods* 
so that they match the x values for which you want to calculate readings.  
Since the *used* table reflects the lag between two adjacent rows, you need 
your *periods* to include the last hour of the previous day and perhaps the 
first hour of the next day (so 26 points, as it were), and then filter it 
afterwards when you plot your graph.  Note that this will be a "snapshot" based 
graph, not an "average" based graph.

This scheme is generally how Process Historians work for continuous control.  
Once each minute (the exact offset into the minute does not really matter nor 
does it need to be the same, it is just frequent readings) the instantaneous 
value is collected and stored together with the computed slope to the previous 
value.  From this you can interpolate what the instantaneous value was at 
exactly each minute boundary.  This gives you a series of polygons which are 
basically estimates of the shape of the curve.  The historian will generate the 
one minute "snapshot" values and average those to get each hourly average.  It 
can also compute the "confidence" in the accuracy of that value based on the 
number of actual samples in the interval (vs the number there ought to be).  
Industry standard values are called "raw" which is the actual recorded data 
with no interpolation, "snapshot" (point in time interpolated values), one 
minute average (which is different from the snapshot since the value usually 
changed sometime during the minute and the average is computed assuming that 
the prior value was in effect up until the change, and the new value 
afterwards, ie, that the readings are discrete -- or it may average the start 
of minute and the end of minute snapshots depending on configuration), six 
minute average on every 6 minute boundary (the average of the 6 one minute 
snapshot values (at minute offset 0, 6, 12 ... in the hour), and hourly average 
on every hour (the average of the 60 one minute snapshot values).  Intervals 
less than a day the y (timestamp) is the at the end of the interval, and for 
intervals greater than a day the y (timestamp) is the beginning of the 
interval.  Sometimes the sample frequency is increased beyond 1 minute, in 
which case the calculations are all based on that sample frequency giving you a 
better approximation of the curve.  (It is all really nothing more than just 
using Eulers method to collect samples which will allow an integral to be 
approximated).

>
>
>> >with pwr (timestamp, reading, ratetoprior) as
>> > (
>> >select julianday(timestamp),
>> >   total_kwh,
>> >   (
>> >  select  (c.total_kwh - p.total_kwh) / (jul

Re: [sqlite] select for power-meter accumulated total readings

[Petr Jakeš]

On Sun, Oct 20, 2019 at 2:53 AM Keith Medcalf  wrote:

> On Saturday, 19 October, 2019 18:26, Petr Jakeš 
> wrote:
>
> >After long time I have set up development environment properly and I am
> >able to start to study your queries.
>
> >I am lost. I don't either understand the first bunch of subqueries... (
> >What is returned in the "ratetoprior"? I have been pulling my hair over 3
> >hours trying to figure it out ... no clue what it means.
>
> RateToPrior is the linear slope of the line which joins the current value
> to the prior value.  Once you know the slope of that line, then for any
> point in time you merely need to find the next reading after that point in
> time and use the slope to calculate/interpolate what the value would have
> been at the time you are interested in (assuming that the value change is
> linear).
>
> Does this help?
>

If I understand it properly:


   - timestamps are considered as x values, readings as y values
   - *pwr* statement calculates slope of the line
    (delta y / delta x) between all
   adjacent readings
   - *periods* statement calculates (fills in) the dates according to the
   requested granity
   - *readings* statement calculates readings from the next reading (next
   higher timestamp to the periods datestamp)
   - *used* statement calculates consumption (lag between two adjacent rows)

My confusion was I didn't expect all values are calculated, not just
returned directly from the database as I was almost doing.
Your approach is GENIAL! Thank you.

Do you mind if I will mention you/your solution on my blog (in the time
when I write post about power/water metering)?

What about if I want 1 hour granity? (to plot a graph of daily consumption
for example)


> >with pwr (timestamp, reading, ratetoprior) as
> > (
> >select julianday(timestamp),
> >   total_kwh,
> >   (
> >  select  (c.total_kwh - p.total_kwh) / (julianday(c.timestamp)-
> >julianday(p.timestamp))
> >  from power as p
> > where julianday(p.timestamp) < julianday(c.timestamp)
> >  order by julianday(p.timestamp) desc
> > limit 1
> >)
> >  from power as c
> >  order by julianday(timestamp)
> > )
> >select * from pwr
> >
> >On Tue, Sep 3, 2019 at 1:11 AM Keith Medcalf  wrote:
> >
> >>
> >> On Monday, 2 September, 2019 12:26, Petr Jakeš
> >
> >> wrote:
> >>
> >> >Yes, you are right. The error is connected with the version  of
> >> >SQLite. Now I am trying to build DB Browser using SQLite version
> >3.29.0.
> >> >Than I have to study your code. Your knowledge and SQL Windows
> >> >functions are over my scope. Thank for the study material for next
> >> weekend :D
> >>
> >> Here is another example for you to ponder that uses your original table
> >> and requires one index.  It builds the "pwr" view dynamically but uses
> >the
> >> ratetoprior to compute the instant readings and only needs one window
> >query
> >> to compute the usage from two readings.  Performance is half way
> >between
> >> the other two examples:
> >>
> >> create index if not exists power_jd on power (julianday(timestamp),
> >> total_kwh);
> >>
> >> with pwr (timestamp, reading, ratetoprior) as
> >>  (
> >> select julianday(timestamp),
> >>total_kwh,
> >>(select (c.total_kwh - p.total_kwh) /
> >> (julianday(c.timestamp) - julianday(p.timestamp))
> >>   from power as p
> >>  where julianday(p.timestamp) < julianday(c.timestamp)
> >>   order by julianday(p.timestamp) desc
> >>  limit 1)
> >>   from power as c
> >>   order by julianday(timestamp)
> >>  ),
> >>  periods (timestamp) as
> >>  (
> >>  select julianday(date(min(timestamp), '-1 day') || '
> >> 23:59:59.999')
> >>from pwr
> >>   union all
> >>  select julianday(datetime(timestamp, '+1 day'))
> >>from periods
> >>   where timestamp < (select max(timestamp) from pwr)
> >>  ),
> >>  readings (timestamp, reading) as
> >>  (
> >>   select timestamp,
> >>  (select reading - (b.timestamp - p.timestamp) *
> >ratetoprior
> >>from pwr as b
> >>   where b.timestamp >= p.timestamp
> >>   limit 1) as reading
> >> from periods as p
> >>where timestamp between (select min(timestamp) from pwr)
> >>and (select max(timestamp) from pwr)
> >> ),
> >> used (timestamp, kwh) as
> >> (
> >>   select timestamp,
> >>  reading - lag(reading) over ()
> >> from readings
> >> )
> >>   select datetime(timestamp),
> >>  kwh
> >> from used
> >>where kwh is not null;
> >>
> >>
> >>
> >> --
> >> The fact that there's a Highway to Hell but only a Stairway to Heaven
> >> says a lot about anticipated traffic volume.
>
> --
> The

Re: [sqlite] select for power-meter accumulated total readings

[Keith Medcalf]

On Saturday, 19 October, 2019 18:26, Petr Jakeš  
wrote:

>After long time I have set up development environment properly and I am
>able to start to study your queries.

>I am lost. I don't either understand the first bunch of subqueries... (
>What is returned in the "ratetoprior"? I have been pulling my hair over 3
>hours trying to figure it out ... no clue what it means.

RateToPrior is the linear slope of the line which joins the current value to 
the prior value.  Once you know the slope of that line, then for any point in 
time you merely need to find the next reading after that point in time and use 
the slope to calculate/interpolate what the value would have been at the time 
you are interested in (assuming that the value change is linear).

Does this help?

>with pwr (timestamp, reading, ratetoprior) as
> (
>select julianday(timestamp),
>   total_kwh,
>   (
>  select  (c.total_kwh - p.total_kwh) / (julianday(c.timestamp)-
>julianday(p.timestamp))
>  from power as p
> where julianday(p.timestamp) < julianday(c.timestamp)
>  order by julianday(p.timestamp) desc
> limit 1
>)
>  from power as c
>  order by julianday(timestamp)
> )
>select * from pwr
>
>On Tue, Sep 3, 2019 at 1:11 AM Keith Medcalf  wrote:
>
>>
>> On Monday, 2 September, 2019 12:26, Petr Jakeš
>
>> wrote:
>>
>> >Yes, you are right. The error is connected with the version  of
>> >SQLite. Now I am trying to build DB Browser using SQLite version
>3.29.0.
>> >Than I have to study your code. Your knowledge and SQL Windows
>> >functions are over my scope. Thank for the study material for next
>> weekend :D
>>
>> Here is another example for you to ponder that uses your original table
>> and requires one index.  It builds the "pwr" view dynamically but uses
>the
>> ratetoprior to compute the instant readings and only needs one window
>query
>> to compute the usage from two readings.  Performance is half way
>between
>> the other two examples:
>>
>> create index if not exists power_jd on power (julianday(timestamp),
>> total_kwh);
>>
>> with pwr (timestamp, reading, ratetoprior) as
>>  (
>> select julianday(timestamp),
>>total_kwh,
>>(select (c.total_kwh - p.total_kwh) /
>> (julianday(c.timestamp) - julianday(p.timestamp))
>>   from power as p
>>  where julianday(p.timestamp) < julianday(c.timestamp)
>>   order by julianday(p.timestamp) desc
>>  limit 1)
>>   from power as c
>>   order by julianday(timestamp)
>>  ),
>>  periods (timestamp) as
>>  (
>>  select julianday(date(min(timestamp), '-1 day') || '
>> 23:59:59.999')
>>from pwr
>>   union all
>>  select julianday(datetime(timestamp, '+1 day'))
>>from periods
>>   where timestamp < (select max(timestamp) from pwr)
>>  ),
>>  readings (timestamp, reading) as
>>  (
>>   select timestamp,
>>  (select reading - (b.timestamp - p.timestamp) *
>ratetoprior
>>from pwr as b
>>   where b.timestamp >= p.timestamp
>>   limit 1) as reading
>> from periods as p
>>where timestamp between (select min(timestamp) from pwr)
>>and (select max(timestamp) from pwr)
>> ),
>> used (timestamp, kwh) as
>> (
>>   select timestamp,
>>  reading - lag(reading) over ()
>> from readings
>> )
>>   select datetime(timestamp),
>>  kwh
>> from used
>>where kwh is not null;
>>
>>
>>
>> --
>> The fact that there's a Highway to Hell but only a Stairway to Heaven
>> says a lot about anticipated traffic volume.

-- 
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

Re: [sqlite] select for power-meter accumulated total readings

[Petr Jakeš]

After long time I have set up development environment properly and I am
able to start to study your queries.

I am lost. I don't either understand the first bunch of subqueries... (
What is returned in the "ratetoprior"? I have been pulling my hair over 3
hours trying to figure it out ... no clue what it means.

with pwr (timestamp, reading, ratetoprior) as
 (
select julianday(timestamp),
   total_kwh,
   (
  select  (c.total_kwh - p.total_kwh) / (julianday(c.timestamp)-
julianday(p.timestamp))
  from power as p
 where julianday(p.timestamp) < julianday(c.timestamp)
  order by julianday(p.timestamp) desc
 limit 1
)
  from power as c
  order by julianday(timestamp)
 )
select * from pwr

On Tue, Sep 3, 2019 at 1:11 AM Keith Medcalf  wrote:

>
> On Monday, 2 September, 2019 12:26, Petr Jakeš 
> wrote:
>
> >Yes, you are right. The error is connected with the version  of
> >SQLite. Now I am trying to build DB Browser using SQLite version 3.29.0.
> >Than I have to study your code. Your knowledge and SQL Windows
> >functions are over my scope. Thank for the study material for next
> weekend :D
>
> Here is another example for you to ponder that uses your original table
> and requires one index.  It builds the "pwr" view dynamically but uses the
> ratetoprior to compute the instant readings and only needs one window query
> to compute the usage from two readings.  Performance is half way between
> the other two examples:
>
> create index if not exists power_jd on power (julianday(timestamp),
> total_kwh);
>
> with pwr (timestamp, reading, ratetoprior) as
>  (
> select julianday(timestamp),
>total_kwh,
>(select (c.total_kwh - p.total_kwh) /
> (julianday(c.timestamp) - julianday(p.timestamp))
>   from power as p
>  where julianday(p.timestamp) < julianday(c.timestamp)
>   order by julianday(p.timestamp) desc
>  limit 1)
>   from power as c
>   order by julianday(timestamp)
>  ),
>  periods (timestamp) as
>  (
>  select julianday(date(min(timestamp), '-1 day') || '
> 23:59:59.999')
>from pwr
>   union all
>  select julianday(datetime(timestamp, '+1 day'))
>from periods
>   where timestamp < (select max(timestamp) from pwr)
>  ),
>  readings (timestamp, reading) as
>  (
>   select timestamp,
>  (select reading - (b.timestamp - p.timestamp) * ratetoprior
>from pwr as b
>   where b.timestamp >= p.timestamp
>   limit 1) as reading
> from periods as p
>where timestamp between (select min(timestamp) from pwr)
>and (select max(timestamp) from pwr)
> ),
> used (timestamp, kwh) as
> (
>   select timestamp,
>  reading - lag(reading) over ()
> from readings
> )
>   select datetime(timestamp),
>  kwh
> from used
>where kwh is not null;
>
>
>
> --
> 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

Re: [sqlite] select for power-meter accumulated total readings

[Keith Medcalf]

On Monday, 2 September, 2019 12:26, Petr Jakeš  wrote:

>Yes, you are right. The error is connected with the version  of
>SQLite. Now I am trying to build DB Browser using SQLite version 3.29.0.
>Than I have to study your code. Your knowledge and SQL Windows
>functions are over my scope. Thank for the study material for next weekend :D

Here is another example for you to ponder that uses your original table and 
requires one index.  It builds the "pwr" view dynamically but uses the 
ratetoprior to compute the instant readings and only needs one window query to 
compute the usage from two readings.  Performance is half way between the other 
two examples:

create index if not exists power_jd on power (julianday(timestamp), total_kwh);

with pwr (timestamp, reading, ratetoprior) as
 (
select julianday(timestamp),
   total_kwh,
   (select (c.total_kwh - p.total_kwh) / (julianday(c.timestamp) - 
julianday(p.timestamp))
  from power as p
 where julianday(p.timestamp) < julianday(c.timestamp)
  order by julianday(p.timestamp) desc
 limit 1)
  from power as c
  order by julianday(timestamp)
 ),
 periods (timestamp) as
 (
 select julianday(date(min(timestamp), '-1 day') || ' 23:59:59.999')
   from pwr
  union all
 select julianday(datetime(timestamp, '+1 day'))
   from periods
  where timestamp < (select max(timestamp) from pwr)
 ),
 readings (timestamp, reading) as
 (
  select timestamp,
 (select reading - (b.timestamp - p.timestamp) * ratetoprior
   from pwr as b
  where b.timestamp >= p.timestamp
  limit 1) as reading
from periods as p
   where timestamp between (select min(timestamp) from pwr)
   and (select max(timestamp) from pwr)
),
used (timestamp, kwh) as
(
  select timestamp,
 reading - lag(reading) over ()
from readings
)
  select datetime(timestamp),
 kwh
from used
   where kwh is not null;



-- 
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

Re: [sqlite] select for power-meter accumulated total readings

[Petr Jakeš]

Yes, you are right. The error is connected with the version  of SQLite. Now
I am trying to build DB Browser using SQLite version 3.29.0.
Than I have to study your code. Your knowledge and SQL Windows functions
are over my scope. Thank for the study material for next weekend :D

On Mon, Sep 2, 2019 at 8:19 PM Keith Medcalf  wrote:

>
> On Monday, 2 September, 2019 10:34, Petr Jakeš 
> wrote:
>
> >Wow, this is HUUUDGE !!!
> >Thanks!
> >What editor are you using, btw?
>
> Typically this is on Windows 10 (for Workstations) and the editor I use is
> TSE (The Semware Editor).  Started using TSE under OS/2 way back and I like
> it a lot and have used it ever since.
>
> >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,"
>
> Perhaps those are using a version of the SQLite engine that is too old.
> Windows functions were added at SQLite Release 3.25.0 on 2018-09-15,
> earlier versions of SQLite will not be able to parse that syntax.
>
> >From  the sqlite3 command line your queries run perfectly, no
> >problems at all.
>
> Glad it could provide some help and demonstrate how to sole that
> particular problem using pure SQL.
>
> --
> 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

Re: [sqlite] select for power-meter accumulated total readings

[Keith Medcalf]

On Monday, 2 September, 2019 10:34, Petr Jakeš  wrote:

>Wow, this is HUUUDGE !!!
>Thanks!
>What editor are you using, btw? 

Typically this is on Windows 10 (for Workstations) and the editor I use is TSE 
(The Semware Editor).  Started using TSE under OS/2 way back and I like it a 
lot and have used it ever since.
 
>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,"

Perhaps those are using a version of the SQLite engine that is too old.  
Windows functions were added at SQLite Release 3.25.0 on 2018-09-15, earlier 
versions of SQLite will not be able to parse that syntax.

>From  the sqlite3 command line your queries run perfectly, no
>problems at all.

Glad it could provide some help and demonstrate how to sole that particular 
problem using pure SQL.

-- 
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

Re: [sqlite] select for power-meter accumulated total readings

[Petr Jakeš]

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  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.
>
>
>
> 

Re: [sqlite] select for power-meter accumulated total readings

[Keith Medcalf]

Of course, what we are emulating here is called a "Process Historian", common 
examples being PHD and PI.  So, if you make a few minor adjustments, you can 
make this run just about as fast as a "designed for purpose" Process Historian. 
 The changes are that you need to store the data in an "economical format".  I 
have chosen to store the timestamp as a floating-point julianday number.  You 
also need to calculate and store the slope to the previous engineering value 
each time you store a value.  You must also insert the data in chronological 
order and you may not update a value once it has been inserted.

This is about 500 times (or more) faster than using the table you created.  The 
following work the same as the previous example but uses triggers to enforce 
the constraints and calculate the slope for new engineering values stored.  And 
you only need to change the '+1 day' to whatever interval you want to use.

This will load the data into the pwr table from your existing table.

drop table pwr;
create table pwr
(
timestamp float primary key,
reading float not null,
ratetoprior float
) without rowid;

create trigger pwr_ins_stamp before insert on pwr
begin
select raise(ABORT, 'Data insertion must be in chronological order')
 where new.timestamp < (select max(timestamp) from pwr);
end;

create trigger pwr_ins_slope after insert on pwr
begin
update pwr
   set ratetoprior = (new.reading - (select reading
   from pwr
  where timestamp < new.timestamp
   order by timestamp desc
  limit 1))
 /
 (new.timestamp - (select timestamp
 from pwr
where timestamp < new.timestamp
 order by timestamp desc
limit 1))
 where timestamp = new.timestamp;
end;

create trigger pwr_upd_error after update of timestamp, reading on pwr
begin
select raise(ABORT, 'Data update prohibited');
end;

insert into pwr (timestamp, reading)
  select julianday(timestamp),
 total_kwh
from power
order by julianday(timestamp);

select datetime(timestamp),
   kwh
  from (
with periods (timestamp) as
(
 select julianday(date(min(timestamp), '-1 day') || ' 23:59:59.999')
   from pwr
union all
 select julianday(datetime(timestamp, '+1 day'))
   from periods
  where timestamp < (select max(timestamp) from pwr)
),
 readings (timestamp, reading) as
(
 select timestamp,
(select reading - (b.timestamp - p.timestamp) * ratetoprior
  from pwr as b
 where b.timestamp >= p.timestamp
 limit 1) as reading
   from periods as p
  where timestamp between (select min(timestamp) from pwr)
  and (select max(timestamp) from pwr)
)
  select timestamp,
 reading - lag(reading) over () as kwh
from readings
   )
where kwh is not null;

-- 
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

Re: [sqlite] select for power-meter accumulated total readings

[Keith Medcalf]

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

Re: [sqlite] select for power-meter accumulated total readings

[Petr Jakeš]

As far I have ended with following:

WITH miniPow as (
select date(TIMESTAMP,'+1 day') as d, max(TOTAL_KWH) mini
from power
group by date(timestamp)
)
, maxiPow as (
select date(TIMESTAMP) as d, max(TOTAL_KWH) maxi
from power
group by date(timestamp)
)
select maxiPow.d, ROUND(maxi-mini, 1) from miniPow
 join
maxiPow
on miniPow.d = maxiPow.d

The only problem is how to calculate average consumption for time gap
(days), when consumption data were not recorded.
Is this possible somehow?

I am thinking about monitor it with an external script (Python) and insert
average virtual data in to the database.

On Thu, Aug 8, 2019 at 9:36 AM Petr Jakeš  wrote:

> I am storing electricity consumption data to the sqlite.
>
> The simple table to store kWh consumption looks like following example
> (accumulated total readings in each row - exactly as you see on your
> electricity meter):
>
> |ID|timestamp|kWh   ||1 | 2019-07-31 14:24:25 | 270.8||2 | 
> 2019-07-31 14:31:02 | 272.1||3 | 2019-08-01 06:56:45 | 382.5||4 | 2019-08-01 
> 16:29:01 | 382.5||5 | 2019-08-01 20:32:53 | 582.5||6 | 2019-08-02 16:18:14 | 
> 612.1|
> |7 | 2019-08-08 07:13:04 | 802.7|
> |..|.|..|
>
>
>- The data interval is not predictable (is random).
>- There can be a day with no records at all (if data transmission
>failure for example).
>- There can be many records with the identical (equal) power
>consumption (no energy consumption) for one or more days.
>
> My question is how to write SQL select to get energy consumption for
> required interval summarized  by days, weeks or months ...
>
> The real challenge is to get an average if for each day for days when
> records were not taken (in the example table days between ID 6 and ID7) -
> each day as a row.
>
> It looks like simple question but I am pulling out my hair for two days to
> find a solution.
>
>
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] select for power-meter accumulated total readings

[Petr Jakeš]

I am storing electricity consumption data to the sqlite.

The simple table to store kWh consumption looks like following example
(accumulated total readings in each row - exactly as you see on your
electricity meter):

|ID|timestamp|kWh   ||1 | 2019-07-31 14:24:25 | 270.8||2 |
2019-07-31 14:31:02 | 272.1||3 | 2019-08-01 06:56:45 | 382.5||4 |
2019-08-01 16:29:01 | 382.5||5 | 2019-08-01 20:32:53 | 582.5||6 |
2019-08-02 16:18:14 | 612.1|
|7 | 2019-08-08 07:13:04 | 802.7|
|..|.|..|


   - The data interval is not predictable (is random).
   - There can be a day with no records at all (if data transmission
   failure for example).
   - There can be many records with the identical (equal) power consumption
   (no energy consumption) for one or more days.

My question is how to write SQL select to get energy consumption for
required interval summarized  by days, weeks or months ...

The real challenge is to get an average if for each day for days when
records were not taken (in the example table days between ID 6 and ID7) -
each day as a row.

It looks like simple question but I am pulling out my hair for two days to
find a solution.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users