select for power-meter accumulated total readings

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