<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
Hallo Andreas,<br>
<br>
es liegen seid 2.4. Daten vor (inzwischen 1450 Liter)<br>
Initialverbrauch 134264 Liter<br>
Der Zählerstand sollten also inzwischen rechnerisch 135714 Liter
sein.<br>
<br>
Hier die Debug-Abfrage:<br>
<br>
<a class="moz-txt-link-freetext" href="http://192.168.178.51/data/xxxxxx-xxxxx-xxxxx-xxxxx-xxxxxxxxxx.json?from=-1&options=raw&debug=1">http://192.168.178.51/data/xxxxxx-xxxxx-xxxxx-xxxxx-xxxxxxxxxx.json?from=-1&options=raw&debug=1</a><br>
<br>
und das Ergebnis:<br>
<br>
<pre class="data">{
"version": "0.3",
"data": {
"tuples": [
[
1617862067814,
145,
1
]
],
"uuid": "xxxxxx-xxxxx-xxxxx-xxxxx-xxxxxxxxxx",
"from": 1617861767833,
"to": 1617862067814,
"average": 0,
"consumption": 0,
"rows": 2
},
"debug": {
"database": "pdo_mysql",
"time": 0.19783,
"uptime": 1177360290,
"load": [
3.78,
4.25,
4.21
],
"commit-hash": "xxxxxxxxxxxxxxxxxxxxxxxxxxx",
"php-version": "7.3.19-1~deb10u1",
"messages": [],
"sql": {
"totalTime": 0.02234506607055664,
"worstTime": 0.0026459693908691406,
"queries": [
{
"sql": "SELECT \n MAX(timestamp) \nFROM \n data \nWHERE \n channel_id = 44 \n AND timestamp < 1617873106000",
"execTime": 0.0026459693908691406
},
{
"sql": "SELECT \n MAX(timestamp) \nFROM \n data \nWHERE \n channel_id = 44 \n AND timestamp < 1617862067814",
"execTime": 0.0012600421905517578
},
{
"sql": "SELECT \n aggregate.type \nFROM \n aggregate \n INNER JOIN entities ON aggregate.channel_id = entities.id \nWHERE \n uuid = 'xxxxxx-xxxxx-xxxxx-xxxxx-xxxxxxxxxx' \n and aggregate.type = 6 \n AND timestamp >= 1617861767833 \n AND timestamp <= 1617862067814 \nLIMIT \n 1",
"execTime": 0.0021820068359375
},
{
"sql": "SELECT \n aggregate.type \nFROM \n aggregate \n INNER JOIN entities ON aggregate.channel_id = entities.id \nWHERE \n uuid = 'xxxxxx-xxxxx-xxxxx-xxxxx-xxxxxxxxxx' \n and aggregate.type = 5 \n AND timestamp >= 1617861767833 \n AND timestamp <= 1617862067814 \nLIMIT \n 1",
"execTime": 0.0018649101257324219
},
{
"sql": "SELECT \n aggregate.type \nFROM \n aggregate \n INNER JOIN entities ON aggregate.channel_id = entities.id \nWHERE \n uuid = 'xxxxxx-xxxxx-xxxxx-xxxxx-xxxxxxxxxx' \n and aggregate.type = 4 \n AND timestamp >= 1617861767833 \n AND timestamp <= 1617862067814 \nLIMIT \n 1",
"execTime": 0.002110004425048828
},
{
"sql": "SELECT \n aggregate.type \nFROM \n aggregate \n INNER JOIN entities ON aggregate.channel_id = entities.id \nWHERE \n uuid = 'xxxxxx-xxxxx-xxxxx-xxxxx-xxxxxxxxxx' \n and aggregate.type = 3 \n AND timestamp >= 1617861767833 \n AND timestamp <= 1617862067814 \nLIMIT \n 1",
"execTime": 0.0017001628875732422
},
{
"sql": "SELECT \n aggregate.type \nFROM \n aggregate \n INNER JOIN entities ON aggregate.channel_id = entities.id \nWHERE \n uuid = 'xxxxxx-xxxxx-xxxxx-xxxxx-xxxxxxxxxx' \n and aggregate.type = 2 \n AND timestamp >= 1617861767833 \n AND timestamp <= 1617862067814 \nLIMIT \n 1",
"execTime": 0.001873016357421875
},
{
"sql": "SELECT \n aggregate.type \nFROM \n aggregate \n INNER JOIN entities ON aggregate.channel_id = entities.id \nWHERE \n uuid = 'xxxxxx-xxxxx-xxxxx-xxxxx-xxxxxxxxxx' \n and aggregate.type = 1 \n AND timestamp >= 1617861767833 \n AND timestamp <= 1617862067814 \nLIMIT \n 1",
"execTime": 0.0018138885498046875
},
{
"sql": "SELECT \n UNIX_TIMESTAMP(\n FROM_UNIXTIME(\n MIN(timestamp) \/ 1000, \n \"%Y-%m-%d %H:%i:00\"\n )\n ) * 1000 \nFROM \n aggregate \nWHERE \n channel_id = 44 \n AND type = '1' \n AND timestamp >= UNIX_TIMESTAMP(\n DATE_ADD(\n FROM_UNIXTIME(\n 1617861767833 \/ 1000, \"%Y-%m-%d %H:%i:00\"\n ), \n INTERVAL 1 minute\n )\n ) * 1000",
"execTime": 0.001316070556640625
},
{
"sql": "SELECT \n UNIX_TIMESTAMP(\n DATE_ADD(\n FROM_UNIXTIME(\n MAX(timestamp) \/ 1000, \n \"%Y-%m-%d %H:%i:00\"\n ), \n INTERVAL 1 minute\n )\n ) * 1000 \nFROM \n aggregate \nWHERE \n channel_id = 44 \n AND type = '1' \n AND timestamp < 1617862067814",
"execTime": 0.0012249946594238281
},
{
"sql": "SELECT \n aggregate.type \nFROM \n aggregate \n INNER JOIN entities ON aggregate.channel_id = entities.id \nWHERE \n uuid = 'xxxxxx-xxxxx-xxxxx-xxxxx-xxxxxxxxxx' \n and aggregate.type = 0 \n AND timestamp >= 1617861767833 \n AND timestamp <= 1617862067814 \nLIMIT \n 1",
"execTime": 0.0016429424285888672
},
{
"sql": "SELECT \n COUNT(1) \nFROM \n data \nWHERE \n channel_id = 44 \n AND timestamp >= 1617861767833 \n AND timestamp <= 1617862067814",
"execTime": 0.001310110092163086
},
{
"sql": "SELECT \n timestamp, \n value, \n 1 AS count \nFROM \n data \nWHERE \n channel_id = 44 \n AND timestamp >= 1617861767833 \n AND timestamp <= 1617862067814 \nORDER BY \n timestamp ASC",
"execTime": 0.0014009475708007812
}
]
}
}
}</pre>
<br>
<br>
Hilft das weiter?<br>
<br>
Gruß<br>
Thomas<br>
<br>
<br>
<br>
<br>
<br>
<div class="moz-cite-prefix">Am 08.04.2021 um 08:10 schrieb Andreas
Goetz:<br>
</div>
<blockquote type="cite"
cite="mid:A1BC3B90-AE35-4DFC-9135-9BBF09FBE334@gmail.com">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<div dir="ltr">Häng mal bitte ein &debug=1 dran, dann sollten
wir sehen welches SQL ausgeführt wird. Vielleicht gibt das einen
Hinweis.</div>
<div dir="ltr"><br>
</div>
<div dir="ltr">Viele Grüße, Andreas</div>
<div dir="ltr"><br>
</div>
<div dir="ltr">PS.: seit wann erfasst Du die Daten?</div>
<div dir="ltr"><br>
<blockquote type="cite">Am 08.04.2021 um 07:59 schrieb
<a class="moz-txt-link-abbreviated" href="mailto:Heine.thomas@online.de">Heine.thomas@online.de</a>:<br>
<br>
</blockquote>
</div>
<blockquote type="cite">
<div dir="ltr">
<meta http-equiv="content-type" content="text/html;
charset=UTF-8">
Hallo,<br>
<br>
ich habe inzwischen meinen Wasserzähler mit einem Reed-Kontakt
ausgestattet und lese ihn per Tasmota ESP8266 über einen
Counter (10l-Schritte) aus.<br>
der Counterwert wird also alle 10l um 1 erhöht.<br>
Im VZ-Logger habe ich einen Kanal mit "Wasser Zählerstände"
angelegt.<br>
Das klappt soweit auch alles. Ich sehe einen Verbrauch, und
auch den Gesamt-Zählerstand nach dem ich einen Initialen
Zählerstand eingetragen habe.<br>
<br>
<div><adillmbjlmnoplia.png></div>
<br>
<br>
<br>
Per HTTP-Request kann ich auch bestimmte Zeit-Bereiche super
abfragen.<br>
<br>
Jetzt würde ich gerne aber an meiner Haus-Visualisierung den
aktuellen Zählerstand anzeigen.<br>
<br>
<br>
So wollte ich ihn abfragen:<br>
<br>
<a class="moz-txt-link-freetext"
href="http://192.168.178.51/data/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxx.json?from=-1&options=raw"
moz-do-not-send="true">http://192.168.178.51/data/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxx.json?from=-1&options=raw</a><br>
<br>
das json sieht jedoch dann so aus:<br>
<pre class="data">{"version":"0.3","data":{"tuples":[[1617860867741,144,1]],"uuid":"xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxx","from":1617860567724,"to":1617860867741,"average":0,"consumption":0,"rows":2}}</pre>
<br>
bzw. so:<br>
<div><lcnmkochlddnejkg.png></div>
<br>
der Zählerstand müsste derzeit irgendwo bei 136xxx Liter
sein...<br>
<br>
Was mache ich bei der Abfrage zum Zählerstand falsch?<br>
<br>
Danke vorab für eure Hilfe<br>
<br>
Gruß<br>
Thomas<br>
</div>
</blockquote>
</blockquote>
<br>
</body>
</html>