Re: [sqlite] [EXTERNAL] select for power-meter accumulated total readings
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 wrote: > > Oops, didn't notice the date of said row was out of sequence wrt rowids. > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] select for power-meter accumulated total readings
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 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 > 66532019-08-06 22:23:26.000 1494.00 > 66552019-07-30 22:32:26.000 150.00 <-- > 66562019-08-07 18:58:17.000 1673.90 > > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] select for power-meter accumulated total readings
Oops, didn't notice the date of said row was out of sequence wrt rowids. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] select for power-meter accumulated total readings
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 66532019-08-06 22:23:26.000 1494.00 66552019-07-30 22:32:26.000 150.00 <-- 66562019-08-07 18:58:17.000 1673.90 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] select for power-meter accumulated total readings
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 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:sqlite-users-boun...@mailinglists.sqlite.org] > Im Auftrag von Petr Jakeš > Gesendet: Donnerstag, 08. August 2019 09:36 > An: sqlite-users@mailinglists.sqlite.org > 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 > sqlite-users@mailinglists.sqlite.org > 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 > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] select for power-meter accumulated total readings
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:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Petr Jakeš Gesendet: Donnerstag, 08. August 2019 09:36 An: sqlite-users@mailinglists.sqlite.org 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 sqlite-users@mailinglists.sqlite.org 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
