{"id":218,"date":"2019-08-08T11:13:02","date_gmt":"2019-08-08T09:13:02","guid":{"rendered":"https:\/\/petr.maxbox.cz\/?p=218"},"modified":"2019-09-01T22:07:46","modified_gmt":"2019-09-01T20:07:46","slug":"sqlite-select-for-power-consumption","status":"publish","type":"post","link":"https:\/\/petr.maxbox.cz\/index.php\/2019\/08\/08\/sqlite-select-for-power-consumption\/","title":{"rendered":"sqlite SELECT for power consumption reading"},"content":{"rendered":"\n<p>So far I have following working code (working with <a href=\"https:\/\/www.maxbox.cz\/nextcloud\/index.php\/s\/HecbRaCjoLCMEbe\">example data<\/a>)<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">WITH miniPow as (\n\tselect date(TIMESTAMP,'+1 day') as d, max(TOTAL_KWH) mini\n\tfrom power \n\tgroup by date(timestamp)\n\t)\n, maxiPow as (\n\tselect date(TIMESTAMP) as d, max(TOTAL_KWH) maxi\n\tfrom power \n\tgroup by date(timestamp)\n\t)\nselect maxiPow.d, ROUND(maxi-mini, 1) from miniPow \n join \nmaxiPow\non miniPow.d = maxiPow.d\t<\/pre>\n\n\n\n<p>Below my first SQL attempt (not really elegant) <\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">WITH consumption AS\n  (SELECT date(TIMESTAMP) AS d,\n          min(TOTAL_KWH) mi,\n          max(TOTAL_KWH) ma\n   FROM power\n   GROUP BY date(TIMESTAMP)),\n     enumerated AS\n  (SELECT *,\n\n     (SELECT count(*)\n      FROM consumption b\n      WHERE a.d >= b.d) AS cnt\n   FROM consumption a),\n     preenumerated AS\n  (SELECT *,\n\n     (SELECT count(*)-1\n      FROM consumption b\n      WHERE a.d >= b.d) AS cnt\n   FROM consumption a)\nSELECT strftime('%d.%m.%Y', en.d) AS \"obdob\u00c3\u00ad od\",\n       strftime('%d.%m.%Y', pre.d) AS \"obdob\u00c3\u00ad do\",\n       printf(\"%.1f\", en.ma) AS \"po\u00c4\u008d\u00c3\u00a1te\u00c4\u008dn\u00c3\u00ad kWh\",\n       printf(\"%.1f\", pre.ma) AS \"koncov\u00c3\u00bd kWh\",\n       printf(\"%.1f\", pre.ma-en.ma) AS \"kWh za obdob\u00c3\u00ad\",\n       (JulianDay(pre.d) - JulianDay(en.d)) AS \"po\u00c4\u008det dn\u00c5\u00af\",\n       printf(\"%.1f\", (pre.ma-en.ma)\/(JulianDay(pre.d) - JulianDay(en.d))) AS \"kWh za den\"\nFROM enumerated en\nJOIN preenumerated pre ON en.cnt = pre.cnt\nUNION ALL\nSELECT strftime('%d.%m.%Y', min(date(TIMESTAMP))) AS \"obdob\u00c3\u00ad od\",\n       strftime('%d.%m.%Y', max(date(TIMESTAMP))) AS \"obdob\u00c3\u00ad do\",\n       \"\" AS \"po\u00c4\u008d\u00c3\u00a1te\u00c4\u008dn\u00c3\u00ad kWh\",\n       \"CELKEM:\" AS \"koncov\u00c3\u00bd kWh\",\n       printf(\"%.1f kWh\", max(TOTAL_KWH)) AS \"kWh za obdob\u00c3\u00ad\",\n       printf(\"za %.0f dn\u00c5\u00af\", (JulianDay(max(date(TIMESTAMP))) - JulianDay(min(date(TIMESTAMP))))) AS \"po\u00c4\u008det dn\u00c5\u00af\",\n       printf(\"\u00c3\u00b8 %.1f kWh\/den\", (max(TOTAL_KWH)-min(TOTAL_KWH))\/(JulianDay(max(date(TIMESTAMP))) - JulianDay(min(date(TIMESTAMP))))) AS \"kWh za den\"\nFROM power<\/pre>\n\n\n\n<pre class=\"wp-block-preformatted\"><a href=\"https:\/\/www.maxbox.cz\/nextcloud\/index.php\/s\/HecbRaCjoLCMEbe\">Link to example table here<\/a><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>So far I have following working code (working with example data) Below my first SQL attempt (not really elegant) Link to example table here<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-218","post","type-post","status-publish","format-standard","hentry","category-nezarazene"],"_links":{"self":[{"href":"https:\/\/petr.maxbox.cz\/index.php\/wp-json\/wp\/v2\/posts\/218","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/petr.maxbox.cz\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/petr.maxbox.cz\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/petr.maxbox.cz\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/petr.maxbox.cz\/index.php\/wp-json\/wp\/v2\/comments?post=218"}],"version-history":[{"count":6,"href":"https:\/\/petr.maxbox.cz\/index.php\/wp-json\/wp\/v2\/posts\/218\/revisions"}],"predecessor-version":[{"id":241,"href":"https:\/\/petr.maxbox.cz\/index.php\/wp-json\/wp\/v2\/posts\/218\/revisions\/241"}],"wp:attachment":[{"href":"https:\/\/petr.maxbox.cz\/index.php\/wp-json\/wp\/v2\/media?parent=218"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/petr.maxbox.cz\/index.php\/wp-json\/wp\/v2\/categories?post=218"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/petr.maxbox.cz\/index.php\/wp-json\/wp\/v2\/tags?post=218"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}