[vz-users] Middleware-Abfrage 4-Wochen Intervall
application MGR
applicationMGR at ecoCuyo.de
Mon Jan 25 20:06:37 CET 2016
H
Hallo Andreas,
hier das Ergebnis mit &debug=1:
{
"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
}
]
}
}
}
Viele Grüße
Armin
> Am 23.01.2016 um 19:54 schrieb Andreas Götz <cpuidle at gmail.com>:
>
> Mhm. Häng mal bitte ein &debug=1 an und poste das ergebnis. Würde gerne das sql sehen...
>
> Viele Grüße, Andreas
>
> Am 23.01.2016 um 19:26 schrieb application MGR <applicationMGR at ecoCuyo.de <mailto:applicationMGR at ecocuyo.de>>:
>
>> Hallo Andreas,
>>
>> hat leider nicht den erwünschten Effekt gebracht. Dieselbe Abfrage
>>
>> http://<localhost>/middleware.php/data.json?from=1450566000000&to=1452985200000&group=week&%20uuid%5B%5D=xxxxxxxx-xxxx-xxxx-xxxx-695ed220d33d
>>
>> wirft nun folgendes aus:
>>
>> {"version":"0.3","data”:[
>> {
>> "tuples”:[
>> [1451170785000,136.755,39831], -> 26.12.15 23:59:45
>> [1451602785000,188.663,28347], -> 31.12.15 23:59:45
>> [1451775585000,42.931,11520], -> 02.01.16 23:59:45
>> [1452380385000,101.307,39865], -> 09.01.16 23:59:45
>> [1452985185000,110.44,39772], -> 16.01.16 23:59:45
>> [1452985230000,0,3] -> 17.01.16 00:00:30
>> ],
>> …
>> }
>> ]}
>>
>> Hab noch nicht recherchiert, was sonst geändert werden muss …
>>
>> Schönes Wochenende
>> Armin
>>
>>
>>> Am 21.01.2016 um 19:29 schrieb Andreas Goetz <cpuidle at gmail.com <mailto:cpuidle at gmail.com>>:
>>>
>>> Keine Ahnung.... funktionierts denn?
>>>
>>> 2016-01-21 19:15 GMT+01:00 application MGR <applicationMGR at ecocuyo.de <mailto:applicationMGR at ecocuyo.de>>:
>>> Hallo Andreas,
>>>
>>> na mir wäre es natürlich recht, nur will ich nichts verschlimmbessern, falls jemand das Feature genau so braucht.
>>>
>>> Gibt es nachteilige Nebenwirkungen, die bedacht werden müssen?
>>>
>>> Best Grüße
>>> Armin
>>>
>>>> Am 21.01.2016 um 13:14 schrieb Andreas Goetz <cpuidle at gmail.com <mailto:cpuidle at gmail.com>>:
>>>>
>>>> 2016-01-21 12:29 GMT+01:00 application MGR <applicationMGR at ecocuyo.de <mailto:applicationMGR at ecocuyo.de>>:
>>>> Hallo @
>>>>
>>>> mit folgender Abfrage mit Startdatum 20.12.15 00:00:00 und Enddatum 17.01.16 00:00:00:
>>>>
>>>> http://<localhost>/middleware.php/data.json?from=1450566000000&to=1452985200000&group=week&%20uuid%5B%5D=xxxxxxxx-xxxx-xxxx-xxxx-695ed220d33d
>>>>
>>>> erhalte ich folgende Antwort von der Middleware:
>>>>
>>>> {"version":"0.3","data”:[
>>>> {
>>>> "tuples”:[
>>>> [1451257185000,159.436,39831], -> 27.12.15 23:59:45
>>>> [1451602785000,155.903,22587], -> 31.12.15 23:59:45
>>>> [1451861985000,50.125,17280], -> 03.01.16 23:59:45
>>>> [1452466785000,138.161,39864], -> 10.01.16 23:59:45
>>>> [1452985230000,75.092,34016] -> 17.01.16 00:00:30
>>>> ],
>>>> ...
>>>> }
>>>> ]}
>>>>
>>>> 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 group=week?
>>>>
>>>> Sehr gute Frage. Die Antwort liegt hier: https://github.com/volkszaehler/volkszaehler.org/blob/master/lib/Interpreter/SQL/MySQLOptimizer.php#L63 <https://github.com/volkszaehler/volkszaehler.org/blob/master/lib/Interpreter/SQL/MySQLOptimizer.php#L63>
>>>>
>>>> Mittlerweile (?) gibts bei MySQL eine Funktion YEARWEEK() die das gewünschte Ergebnis bringen sollte.
>>>>
>>>>
>>>> Viele Grüße
>>>> Armin
>>>>
>>>> Wollen wir das ändern?
>>>>
>>>> Viele Grüße,
>>>> Andreas
>>>
>>>
>>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://demo.volkszaehler.org/pipermail/volkszaehler-users/attachments/20160125/b9c15a8d/attachment.html>
More information about the volkszaehler-users
mailing list