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 <h...@scigames.at> 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
