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
