select for power-meter accumulated total readings

classic Classic list List threaded Threaded
23 messages Options
12
Reply | Threaded
Open this post in threaded view
|

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Hick Gunter
I see two subproblems in this query

a) estimating total electricity consumption for points in time that do not have an entry
b) generating regular points in time

ad a) assume a linear consumption of power between two measurements

So for a time tx that is between ta and tb with values of pa and pb respectively, px = pa + (tx -ta) * (pb -pa) / (tb - ta)

You only need to extrapolate if you have a point in time that has measurements only on one side. And you have to decide which average consumption to use (daily/weekly/monthly/seasonal/yearly average) for extrapolation.

tx < ta : px = pa + (ta -tx) * (pb -pa) / (tb -ta)
tx > tb: px = pb + (tx -tb) * (pb -pa) / (tb -ta)

ad b) generate regular points in time

Use a recursive CTE or the sequence generator eponymous table for current day = (starting day + sequence number * 1 day)

Then join your extrapolation query to your time sequence generator for the results

Once you have accumulated enough mesurements, you could do a fourier analysis to quantify daily/weekly/yearly variations and a "base load"

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Petr Jakeš
Gesendet: Donnerstag, 08. August 2019 09:36
An: [hidden email]
Betreff: [EXTERNAL] [sqlite] select for power-meter accumulated total readings

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


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Petr Jakeš
Thank you.
To make thing easier I would expect linear extrapolation between missing
dates.
So fare I have written this SELECT (example data included)
<https://petr.maxbox.cz/index.php/2019/08/08/sqlite-select-for-power-consumption/?preview=true>
(I am not sure how to format the code properly here in the mailing list, so
I did put it on the web.



On Thu, Aug 8, 2019 at 10:21 AM Hick Gunter <[hidden email]> wrote:

> I see two subproblems in this query
>
> a) estimating total electricity consumption for points in time that do not
> have an entry
> b) generating regular points in time
>
> ad a) assume a linear consumption of power between two measurements
>
> So for a time tx that is between ta and tb with values of pa and pb
> respectively, px = pa + (tx -ta) * (pb -pa) / (tb - ta)
>
> You only need to extrapolate if you have a point in time that has
> measurements only on one side. And you have to decide which average
> consumption to use (daily/weekly/monthly/seasonal/yearly average) for
> extrapolation.
>
> tx < ta : px = pa + (ta -tx) * (pb -pa) / (tb -ta)
> tx > tb: px = pb + (tx -tb) * (pb -pa) / (tb -ta)
>
> ad b) generate regular points in time
>
> Use a recursive CTE or the sequence generator eponymous table for current
> day = (starting day + sequence number * 1 day)
>
> Then join your extrapolation query to your time sequence generator for the
> results
>
> Once you have accumulated enough mesurements, you could do a fourier
> analysis to quantify daily/weekly/yearly variations and a "base load"
>
> -----Ursprüngliche Nachricht-----
> Von: sqlite-users [mailto:[hidden email]]
> Im Auftrag von Petr Jakeš
> Gesendet: Donnerstag, 08. August 2019 09:36
> An: [hidden email]
> Betreff: [EXTERNAL] [sqlite] select for power-meter accumulated total
> readings
>
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___________________________________________
>  Gunter Hick | Software Engineer | Scientific Games International GmbH |
> Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O)
> +43 1 80100 - 0
>
> May be privileged. May be confidential. Please delete if not the addressee.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Jean-Christophe Deschamps-3
Beware that row id 6655 isn't correct (total_kwh is suddenly decreasing).
This inconsistancy maybe related to row 6654 missing: manipulated data?
So you'd have to sanitize your data first.

ID TIMESTAMP TOTAL_KWH
6653 2019-08-06 22:23:26.000 1494.00
6655 2019-07-30 22:32:26.000 150.00         <--
6656 2019-08-07 18:58:17.000 1673.90

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Jean-Christophe Deschamps-3
In reply to this post by Petr Jakeš

Oops, didn't notice the date of said row was out of sequence wrt rowids.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Petr Jakeš
In reply to this post by Petr Jakeš
An ID is just an ID. Unique mark in the row. No other meaning.
So sanitizing not necessary, AFIK.

On Thu, Aug 8, 2019 at 11:59 AM Jean-Christophe Deschamps <[hidden email]>
wrote:

> Beware that row id 6655 isn't correct (total_kwh is suddenly decreasing).
> This inconsistancy maybe related to row 6654 missing: manipulated data?
> So you'd have to sanitize your data first.
>
> ID      TIMESTAMP       TOTAL_KWH
> 6653    2019-08-06 22:23:26.000 1494.00
> 6655    2019-07-30 22:32:26.000 150.00         <--
> 6656    2019-08-07 18:58:17.000 1673.90
>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Petr Jakeš
In reply to this post by Petr Jakeš
Yes, I have edited data to have a power consumption in indicated date. You
are very precise (observant), BTW :D

On Thu, Aug 8, 2019 at 12:10 PM Jean-Christophe Deschamps <[hidden email]>
wrote:

>
> Oops, didn't notice the date of said row was out of sequence wrt rowids.
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: select for power-meter accumulated total readings

Petr Jakeš
In reply to this post by 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š <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: select for power-meter accumulated total readings

Keith Medcalf
In reply to this post by Petr Jakeš
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: select for power-meter accumulated total readings

Petr Jakeš
In reply to this post by Keith Medcalf
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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: select for power-meter accumulated total readings

Keith Medcalf

On Monday, 2 September, 2019 10:34, Petr Jakeš <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> wrote:

>
> On Monday, 2 September, 2019 10:34, Petr Jakeš <[hidden email]>
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: select for power-meter accumulated total readings

Keith Medcalf

On Monday, 2 September, 2019 12:26, Petr Jakeš <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> wrote:

>
> On Monday, 2 September, 2019 12:26, Petr Jakeš <[hidden email]>
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: select for power-meter accumulated total readings

Keith Medcalf
On Saturday, 19 October, 2019 18:26, Petr Jakeš <[hidden email]> 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 <[hidden email]> wrote:
>
>>
>> On Monday, 2 September, 2019 12:26, Petr Jakeš
><[hidden email]>
>> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: select for power-meter accumulated total readings

Petr Jakeš
On Sun, Oct 20, 2019 at 2:53 AM Keith Medcalf <[hidden email]> wrote:

> On Saturday, 19 October, 2019 18:26, Petr Jakeš <[hidden email]>
> 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
   <https://en.wikipedia.org/wiki/Slope> (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 <[hidden email]> wrote:
> >
> >>
> >> On Monday, 2 September, 2019 12:26, Petr Jakeš
> ><[hidden email]>
> >> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: select for power-meter accumulated total readings

Keith Medcalf

On Sunday, 20 October, 2019 06:58, Petr Jakeš <[hidden email]> wrote:

>On Sun, Oct 20, 2019 at 2:53 AM Keith Medcalf <[hidden email]> wrote:

>> On Saturday, 19 October, 2019 18:26, Petr Jakeš <[hidden email]> 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
>   <https://en.wikipedia.org/wiki/Slope> (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) / (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 <[hidden email]>
>wrote:
>> >
>> >>
>> >> On Monday, 2 September, 2019 12:26, Petr Jakeš
>> ><[hidden email]>
>> >> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: select for power-meter accumulated total readings

Petr Jakeš
On Sun, Oct 20, 2019 at 4:36 PM Keith Medcalf <[hidden email]> wrote:

>
> On Sunday, 20 October, 2019 06:58, Petr Jakeš <[hidden email]>
> wrote:
>
> >On Sun, Oct 20, 2019 at 2:53 AM Keith Medcalf <[hidden email]>
> wrote:
>
> >> On Saturday, 19 October, 2019 18:26, Petr Jakeš <
> [hidden email]> 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
> >   <https://en.wikipedia.org/wiki/Slope> (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).
>

Thank you for nice lesson. I was briefly googling about Process Historians.
Thanks to your help I know, SQLite is powerful enough for my purposes (to
store 9 water meters  and 9 power meters readings). All depends, of course,
about the time granularity of stored readings. In my case, one reading a
day (water) and 144 readings a day (energy) per spot will be enough.



> >
> >
> >> >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 <[hidden email]>
> >wrote:
> >> >
> >> >>
> >> >> On Monday, 2 September, 2019 12:26, Petr Jakeš
> >> ><[hidden email]>
> >> >> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: select for power-meter accumulated total readings

Petr Jakeš
On Sun, Oct 20, 2019 at 8:23 PM Petr Jakeš <[hidden email]> wrote:

>
>
> On Sun, Oct 20, 2019 at 4:36 PM Keith Medcalf <[hidden email]> wrote:
>
>>
>> On Sunday, 20 October, 2019 06:58, Petr Jakeš <[hidden email]>
>> wrote:
>>
>> >On Sun, Oct 20, 2019 at 2:53 AM Keith Medcalf <[hidden email]>
>> wrote:
>>
>> >> On Saturday, 19 October, 2019 18:26, Petr Jakeš <
>> [hidden email]> 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
>> >   <https://en.wikipedia.org/wiki/Slope> (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).
>>
>
> Thank you for nice lesson. I was briefly googling about Process
> Historians. Thanks to your help I know, SQLite is powerful enough for my
> purposes (to store 9 water meters  and 9 power meters readings). All
> depends, of course, about the time granularity of stored readings. In my
> case, one reading a day (water) and 144 readings a day (energy) per spot
> will be enough.
>

1 hour granularity works as expected...

         select julianday(strftime('%Y-%m-%d %H',(min(timestamp)), '-1
hour') || ':00:00.000')
           from pwr
      union all
         select julianday(datetime(timestamp, '+1 hour'))
           from periods
          where timestamp < (select max(timestamp) from pwr)


>
>
>
>> >
>> >
>> >> >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 <[hidden email]>
>> >wrote:
>> >> >
>> >> >>
>> >> >> On Monday, 2 September, 2019 12:26, Petr Jakeš
>> >> ><[hidden email]>
>> >> >> 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
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
12