select for power-meter accumulated total readings

classic Classic list List threaded Threaded
14 messages Options
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