<html><head><meta http-equiv="Content-Type" content="text/html charset=utf-8"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;" class=""><div class=""><pre style="word-wrap: break-word; white-space: pre-wrap;" class=""><font face="Arial" class="">H</font></pre><pre style="word-wrap: break-word; white-space: pre-wrap;" class=""><font face="Arial" class="">Hallo Andreas,</font></pre><pre style="word-wrap: break-word; white-space: pre-wrap;" class=""><font face="Arial" class="">hier das Ergebnis mit &debug=1:</font></pre><pre style="word-wrap: break-word; white-space: pre-wrap;" class="">{
"version": "0.3",
"data": [
{
"tuples": [
[
1451170785000,
136.755,
39831
],
[
1451602785000,
188.663,
28347
],
[
1451775585000,
42.931,
11520
],
[
1452380385000,
101.307,
39865
],
[
1452985185000,
110.44,
39772
],
[
1452985230000,
0,
3
]
],
"uuid": “xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx",
"from": 1450565985000,
"to": 1452985230000,
"min": [
1452985230000,
0
],
"max": [
1451602785000,
188.6625
],
"average": 123.879,
"consumption": 83248.5,
"rows": 7
}
],
"debug": {
"level": "1",
"database": "pdo_mysql",
"time": 2.85656,
"uptime": 558679120,
"load": [
0.41,
0.35,
0.33
],
"commit-hash": "3bbeb9f96e7b9a795b1fdd38f272946dfc6064f8",
"php-version": "5.6.14-0+deb8u1",
"messages": [],
"sql": {
"totalTime": 2.8117651939392,
"worstTime": 1.4451360702515,
"queries": [
{
"sql": "SELECT MIN(timestamp) FROM (SELECT timestamp FROM data WHERE channel_id=? AND timestamp<? ORDER BY timestamp DESC LIMIT 2) t",
"params": [
3,
1450566000000
],
"types": [],
"executionMS": 1.4451360702515
},
{
"sql": "SELECT MAX(timestamp) FROM (SELECT timestamp FROM data WHERE channel_id=? AND timestamp>? ORDER BY timestamp ASC LIMIT 2) t",
"params": [
3,
1452985200000
],
"types": [],
"executionMS": 0.020693063735962
},
{
"sql": "SELECT aggregate.type, COUNT(aggregate.id) AS count FROM aggregate INNER JOIN entities ON aggregate.channel_id = entities.id WHERE uuid = ? AND aggregate.type <= ? GROUP BY type HAVING count > 0 ORDER BY type DESC",
"params": [
"xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx",
4
],
"types": [],
"executionMS": 1.3102521896362
},
{
"sql": "SELECT UNIX_TIMESTAMP(FROM_UNIXTIME(MIN(timestamp) \/ 1000, \"%Y-%m-%d\")) * 1000 FROM aggregate WHERE channel_id=? AND type=? AND UNIX_TIMESTAMP(FROM_UNIXTIME(timestamp \/ 1000, \"%Y-%m-%d\")) * 1000 >=?",
"params": [
3,
3,
1450565970000
],
"types": [],
"executionMS": 0.009148120880127
},
{
"sql": "SELECT UNIX_TIMESTAMP(DATE_ADD(FROM_UNIXTIME(MAX(timestamp) \/ 1000, \"%Y-%m-%d\"), INTERVAL 1 day)) * 1000 FROM aggregate WHERE channel_id=? AND type=? AND timestamp<?",
"params": [
3,
3,
1452985230000
],
"types": [],
"executionMS": 0.0017869472503662
},
{
"sql": "SELECT COUNT(1) FROM (SELECT DISTINCT YEAR(FROM_UNIXTIME(timestamp\/1000)), YEARWEEK(FROM_UNIXTIME(timestamp\/1000)) FROM data WHERE channel_id = ? AND ( timestamp >= ? AND timestamp < ? OR timestamp >= ? AND timestamp <= ?) UNION SELECT DISTINCT YEAR(FROM_UNIXTIME(timestamp\/1000)), YEARWEEK(FROM_UNIXTIME(timestamp\/1000)) FROM aggregate WHERE channel_id = ? AND type = ? AND timestamp >= ? AND timestamp < ?) AS agg",
"params": [
3,
1450565970000,
"1450566000000",
"1452985200000",
1452985230000,
3,
3,
"1450566000000",
"1452985200000"
],
"types": [],
"executionMS": 0.0064718723297119
},
{
"sql": "SELECT MAX(timestamp) AS timestamp, MAX(value) AS value, SUM(count) AS count FROM (SELECT timestamp, value, 1 AS count FROM data WHERE channel_id = ? AND ( timestamp >= ? AND timestamp < ? OR timestamp >= ? AND timestamp <= ?) UNION SELECT timestamp, value, count FROM aggregate WHERE channel_id = ? AND type = ? AND timestamp >= ? AND timestamp < ?) AS agg GROUP BY YEAR(FROM_UNIXTIME(timestamp\/1000)), YEARWEEK(FROM_UNIXTIME(timestamp\/1000)) ORDER BY timestamp ASC",
"params": [
3,
1450565970000,
"1450566000000",
"1452985200000",
1452985230000,
3,
3,
"1450566000000",
"1452985200000"
],
"types": [],
"executionMS": 0.018276929855347
}
]
}
}
}</pre><div class=""><br class=""></div></div><div class=""><br class=""></div><div class=""><br class=""></div><div class="">Viele Grüße</div><div class="">Armin</div><div class=""><br class=""></div><br class=""><div><blockquote type="cite" class=""><div class="">Am 23.01.2016 um 19:54 schrieb Andreas Götz <<a href="mailto:cpuidle@gmail.com" class="">cpuidle@gmail.com</a>>:</div><br class="Apple-interchange-newline"><div class=""><meta http-equiv="content-type" content="text/html; charset=utf-8" class=""><div dir="auto" class=""><div class=""></div><div class="">Mhm. Häng mal bitte ein &debug=1 an und poste das ergebnis. Würde gerne das sql sehen...</div><div class=""><br class=""></div><div class="">Viele Grüße, Andreas </div><div class=""><br class="">Am 23.01.2016 um 19:26 schrieb application MGR <<a href="mailto:applicationMGR@ecocuyo.de" class="">applicationMGR@ecoCuyo.de</a>>:<br class=""><br class=""></div><blockquote type="cite" class=""><div class=""><meta http-equiv="Content-Type" content="text/html charset=utf-8" class=""><div class="">Hallo Andreas,</div><div class=""><br class=""></div><div class="">hat leider nicht den erwünschten Effekt gebracht. Dieselbe Abfrage </div><div class=""><br class=""></div><div class="">http://<localhost>/middleware.php/data.json?from=1450566000000&to=1452985200000&group=week&%20uuid%5B%5D=xxxxxxxx-xxxx-xxxx-xxxx-695ed220d33d</div><div class=""><br class=""></div><div class="">wirft nun folgendes aus:</div><div class=""><br class=""></div><div class="">{"version":"0.3","data”:[</div><div class=""><span class="Apple-tab-span" style="white-space:pre"> </span>{</div><div class=""><span class="Apple-tab-span" style="white-space:pre"> </span>"tuples”:[</div><div class=""><span class="Apple-tab-span" style="white-space:pre"> </span>[1451170785000,136.755,39831],<span class="Apple-tab-span" style="white-space:pre"> </span>-><span class="Apple-tab-span" style="white-space:pre"> </span>26.12.15 23:59:45</div><div class=""><span class="Apple-tab-span" style="white-space:pre"> </span>[1451602785000,188.663,28347],<span class="Apple-tab-span" style="white-space:pre"> </span>-><span class="Apple-tab-span" style="white-space:pre"> </span>31.12.15 23:59:45</div><div class=""><span class="Apple-tab-span" style="white-space:pre"> </span>[1451775585000,42.931,11520],<span class="Apple-tab-span" style="white-space:pre"> </span>-><span class="Apple-tab-span" style="white-space:pre"> </span>02.01.16 23:59:45</div><div class=""><span class="Apple-tab-span" style="white-space:pre"> </span>[1452380385000,101.307,39865],<span class="Apple-tab-span" style="white-space:pre"> </span>-><span class="Apple-tab-span" style="white-space:pre"> </span>09.01.16 23:59:45</div><div class=""><span class="Apple-tab-span" style="white-space:pre"> </span>[1452985185000,110.44,39772],<span class="Apple-tab-span" style="white-space:pre"> </span>-><span class="Apple-tab-span" style="white-space:pre"> </span>16.01.16 23:59:45</div><div class=""><span class="Apple-tab-span" style="white-space:pre"> </span>[1452985230000,0,3]<span class="Apple-tab-span" style="white-space:pre"> </span>-><span class="Apple-tab-span" style="white-space:pre"> </span>17.01.16 00:00:30</div><div class=""><span class="Apple-tab-span" style="white-space:pre"> </span>],</div><div class=""><span class="Apple-tab-span" style="white-space:pre"> </span>…</div><div class=""><span class="Apple-tab-span" style="white-space:pre"> </span>}</div><div class="">]}</div><div class=""><br class=""></div><div class="">Hab noch nicht recherchiert, was sonst geändert werden muss …</div><div class=""><br class=""></div><div class="">Schönes Wochenende</div><div class="">Armin</div><div class=""><br class=""></div><br class=""><div class=""><blockquote type="cite" class=""><div class="">Am 21.01.2016 um 19:29 schrieb Andreas Goetz <<a href="mailto:cpuidle@gmail.com" class="">cpuidle@gmail.com</a>>:</div><br class="Apple-interchange-newline"><div class=""><div dir="ltr" class="">Keine Ahnung.... funktionierts denn?<br class=""></div><div class="gmail_extra"><br class=""><div class="gmail_quote">2016-01-21 19:15 GMT+01:00 application MGR <span dir="ltr" class=""><<a href="mailto:applicationMGR@ecocuyo.de" target="_blank" class="">applicationMGR@ecocuyo.de</a>></span>:<br class=""><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div style="word-wrap:break-word" class="">Hallo Andreas,<div class=""><br class=""></div><div class="">na mir wäre es natürlich recht, nur will ich nichts verschlimmbessern, falls jemand das Feature genau so braucht.</div><div class=""><br class=""></div><div class="">Gibt es nachteilige Nebenwirkungen, die bedacht werden müssen?</div><div class=""><br class=""></div><div class="">Best Grüße</div><div class="">Armin</div><div class=""><div class="h5"><div class=""><br class=""><div class=""><blockquote type="cite" class=""><div class="">Am 21.01.2016 um 13:14 schrieb Andreas Goetz <<a href="mailto:cpuidle@gmail.com" target="_blank" class="">cpuidle@gmail.com</a>>:</div><br class=""><div class=""><div dir="ltr" style="font-family:Helvetica;font-size:12px;font-style:normal;font-variant:normal;font-weight:normal;letter-spacing:normal;line-height:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px" class=""><div class="gmail_extra"><div class="gmail_quote">2016-01-21 12:29 GMT+01:00 application MGR<span class=""> </span><span dir="ltr" class=""><<a href="mailto:applicationMGR@ecocuyo.de" target="_blank" class="">applicationMGR@ecocuyo.de</a>></span>:<br class=""><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-style:solid;border-left-color:rgb(204,204,204);padding-left:1ex"><div style="word-wrap:break-word" class=""><div class="">Hallo @</div><div class=""><br class=""></div><div class="">mit folgender Abfrage mit Startdatum<span class=""> </span><i class="">20.12.15 00:00:00</i><span class=""> </span>und Enddatum<span class=""> </span><i class="">17.01.16 00:00:00:</i></div><div class=""><br class=""></div><div class="">http://<localhost>/middleware.php/data.json?from=1450566000000&to=1452985200000&group=week&%20uuid%5B%5D=xxxxxxxx-xxxx-xxxx-xxxx-695ed220d33d</div><div class=""><br class=""></div><div class="">erhalte ich folgende Antwort von der Middleware:</div><div class=""><br class=""></div><div class="">{"version":"0.3","data”:[</div><div class=""><span style="white-space:pre-wrap" class=""> </span>{</div><div class=""><span style="white-space:pre-wrap" class=""> </span>"tuples”:[</div><div class=""><span style="white-space:pre-wrap" class=""> </span>[1451257185000,159.436,39831], <span style="white-space:pre-wrap" class=""> </span>-> <span style="white-space:pre-wrap" class=""> </span>27.12.15 23:59:45</div><div class=""><span style="white-space:pre-wrap" class=""> </span>[1451602785000,155.903,22587],<span style="white-space:pre-wrap" class=""> </span>-><span style="white-space:pre-wrap" class=""> </span>31.12.15 23:59:45<span style="white-space:pre-wrap" class=""> </span></div><div class=""><span style="white-space:pre-wrap" class=""> </span>[1451861985000,50.125,17280],<span style="white-space:pre-wrap" class=""> </span>-<font color="#e63b7a" class=""><b class="">><span style="white-space:pre-wrap" class=""> </span>03.01.16 23:59:45</b></font></div><div class=""><span style="white-space:pre-wrap" class=""> </span>[1452466785000,138.161,39864],<span style="white-space:pre-wrap" class=""> </span>-><span style="white-space:pre-wrap" class=""> </span>10.01.16 23:59:45</div><div class=""><span style="white-space:pre-wrap" class=""> </span>[1452985230000,75.092,34016]<span style="white-space:pre-wrap" class=""> </span>-><span style="white-space:pre-wrap" class=""> </span>17.01.16 00:00:30</div><div class=""><span style="white-space:pre-wrap" class=""> </span>],</div>...<div class=""><span style="white-space:pre-wrap" class=""> </span>}</div><div class="">]}</div><div class=""><br class=""></div><div class="">Wer kann mir sagen, warum der 31.12. hier als tuple mit-ausgegeben wird? Hat der letzte Tag des Jahres eine besondere Bedeutung für die<i class=""><span class=""> </span>group=week</i>?</div></div></blockquote><div class=""><br class=""></div><div class="">Sehr gute Frage. Die Antwort liegt hier:<span class=""> </span><a href="https://github.com/volkszaehler/volkszaehler.org/blob/master/lib/Interpreter/SQL/MySQLOptimizer.php#L63" target="_blank" class="">https://github.com/volkszaehler/volkszaehler.org/blob/master/lib/Interpreter/SQL/MySQLOptimizer.php#L63</a><br class=""><br class=""></div><div class="">Mittlerweile (?) gibts bei MySQL eine Funktion YEARWEEK() die das gewünschte Ergebnis bringen sollte.<br class=""><br class=""></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-style:solid;border-left-color:rgb(204,204,204);padding-left:1ex"><div style="word-wrap:break-word" class=""><div class=""><br class=""></div><div class="">Viele Grüße</div><div class="">Armin</div></div></blockquote></div><br class=""></div><div class="gmail_extra">Wollen wir das ändern?<br class=""><br class=""></div><div class="gmail_extra">Viele Grüße,<br class=""></div><div class="gmail_extra">Andreas</div></div></div></blockquote></div><br class=""></div></div></div></div></blockquote></div><br class=""></div>
</div></blockquote></div><br class=""></div></blockquote></div></div></blockquote></div><br class=""></body></html>