<html><head><meta http-equiv="content-type" content="text/html; charset=utf-8"></head><body dir="auto"><div></div><div>Argh, Denkfehler. Kann es sein dass Du bereits nach Wochen aggregierst? Falls ja lösch für den ersten Test bitte mal die Aggregationstabelle. Wenn das klappt sollte es auch danach gehen- nur stehen da ja und leider schon die falschen Daten drin...</div><div><br></div><div>Viele Grüße, Andreas </div><div><br>Am 25.01.2016 um 20:06 schrieb application MGR <<a href="mailto:applicationMGR@ecocuyo.de">applicationMGR@ecoCuyo.de</a>>:<br><br></div><blockquote type="cite"><div><meta http-equiv="Content-Type" content="text/html charset=utf-8"><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=""></div></blockquote></body></html>