Grafana-Abfragen von iobroker MySQL

[sissiwup]

@Dutchman:

Hi Sissiwup,

Danke dir der Serie override hat das Problem mit dem stacking gelöst diese Funktion hatte ich übersehen

Das letztere ergibt leider nicht nicht das gewünschte Resultat, wenn hover auf kumultatief wird der Gesamtwert auf dem bestehenden datenpunkt wiedergegeben.

Dadurch hat mein ein falsches Bild ( zb Strom Abnahme Nacht ist nicht jetziger Wert sondern Nacht + Tag)

Resultat sollte aber sein Nacht = jetziger Wert, Tag = jetziger Wert und dan ein total Nacht + Tag.

~~<link_text text="https://uploads.tapatalk-cdn.com/201804 ... e3eb19.jpg">https://uploads.tapatalk-cdn.com/20180422/29f94a17332e670d47d9c2b614e3eb19.jpg</link_text>" />

~~<link_text text="https://uploads.tapatalk-cdn.com/201804 ... d5ac26.jpg">https://uploads.tapatalk-cdn.com/20180422/b949764b71f68d7467d3f9e2fbd5ac26.jpg</link_text>" />

Verzonden vanaf mijn iPad met Tapatalk~~~~ ` ~~~~Hallo,

das lässt sich vermutlich mit ein bisschen SQL-Magic lösen:

SELECT
  time_sec,
  sum(t.value) as value,
  "Strom (Summe)" as metric
FROM 
((select cast(cast(n.ts/1000/600 as signed) * 600 as signed)  as time_sec,
  avg(n.val) as value 
  from ts_number n where $__unixEpochFrom()*1000<n.ts and/$__unixepochto()*1000="">n.ts and n.id = 110 
  group by time_sec)
  union all 
  (select cast(cast(n.ts/1000/600 as signed) * 600 as signed)  as time_sec,
  avg(n.val) as value 
  from ts_number n where $__unixEpochFrom()*1000<n.ts and/$__unixepochto()*1000="">n.ts and n.id = 107 
  group by time_sec)
  ) t
WHERE $__unixEpochFrom()<t.time_sec and/$__unixepochto()="">t.time_sec   
group by t.time_sec
ORDER BY t.time_sec</t.time_sec></n.ts></n.ts>

Wie gehts: die id`s sind die deiner beiden Datenpunkte.

Es wird der Durchschnitt für 10 Minuten (daher die 600) berechnet.

Das wird dann unter den graden Wert gespeichert, also 6:00,6:10,6:20,6:30,6:40 …

Dann werden einfach beide Werte addiert, da sie ja jetzt immer zu exakt selben Zeit vorliegen.

Das Intervall sollte so gewählt werden, das sicher von beiden Adaptern ein Wert vorhanden ist.

Das kannst du auch beinflussen indem du im SQL-Adapter z.B. einstellt alle 9 Minuten Speichern.

Auch ohne Änderung.~~~~

[piForscher]

Hallo,

ich habe nochmal eine SQL-lastige Frage:

Wenn ich einen Verbrauchswert habe, der nur einmal am Tag geschrieben (erhöht) wird:

Wie bekomme ich mit SQL diese Differenz raus (also die Differenz zwischen dem aktuellen und dem gestrigen Wert?

Dank eurer Hilfe habe ich diese Abfrage, die mir eine Tabelle füllt - aber leider immer nur mit 0, weil ja nur ein Wert geschrieben wird, also die Different zwischen min und max 0 ist.

SELECT CONCAT(MONTH(FROM_UNIXTIME(substring(ts,1,10))),'-', DAY(FROM_UNIXTIME(substring(ts,1,10)))) 
AS Tag, round(max(val)-min(val),0) AS Solarpumpe 
FROM iobroker.ts_number 
WHERE  id=(SELECT id FROM iobroker.datapoints WHERE name="viessmann.0.get.VitoLaufzeitSolar") 
AND FROM_UNIXTIME(substring(ts,1,10)) BETWEEN DATE_SUB(CURRENT_DATE(),INTERVAL 14 day) 
AND CURRENT_DATE() 
GROUP BY MONTH(FROM_UNIXTIME(substring(ts,1,10))),DAY(FROM_UNIXTIME(substring(ts,1,10))) 
ORDER BY MONTH(FROM_UNIXTIME(substring(ts,1,10))) , DAY(FROM_UNIXTIME(substring(ts,1,10)))

[sissiwup]

@piForscher:

Hallo,

ich habe nochmal eine SQL-lastige Frage:

Wenn ich einen Verbrauchswert habe, der nur einmal am Tag geschrieben (erhöht) wird:

Wie bekomme ich mit SQL diese Differenz raus (also die Differenz zwischen dem aktuellen und dem gestrigen Wert?

Dank eurer Hilfe habe ich diese Abfrage, die mir eine Tabelle füllt - aber leider immer nur mit 0, weil ja nur ein Wert geschrieben wird, also die Different zwischen min und max 0 ist.

SELECT CONCAT(MONTH(FROM_UNIXTIME(substring(ts,1,10))),'-', DAY(FROM_UNIXTIME(substring(ts,1,10)))) 
AS Tag, round(max(val)-min(val),0) AS Solarpumpe 
FROM iobroker.ts_number 
WHERE  id=(SELECT id FROM iobroker.datapoints WHERE name="viessmann.0.get.VitoLaufzeitSolar") 
AND FROM_UNIXTIME(substring(ts,1,10)) BETWEEN DATE_SUB(CURRENT_DATE(),INTERVAL 14 day) 
AND CURRENT_DATE() 
GROUP BY MONTH(FROM_UNIXTIME(substring(ts,1,10))),DAY(FROM_UNIXTIME(substring(ts,1,10))) 
ORDER BY MONTH(FROM_UNIXTIME(substring(ts,1,10))) , DAY(FROM_UNIXTIME(substring(ts,1,10)))
```` `  

Hmmm. Das geht bei der Datenfülle in den Tabellen nicht auf den üblichen Wegen

(left join mit sich selbst mit dem max(datum) < aktuelle Zeile-Datum)

(so ähnlich wie unten / ungetestet!!!)

SELECT
    t1.ID,
    t1.val,
    t1.ts,
    t1.val - IFNULL(t2.val, 0) AS diff_val   
FROM
    ts_number t1
    LEFT JOIN ts_number t2
        ON t2.ts = (
            SELECT MAX(ts)
            FROM ts_number t3
            WHERE t3.ts < t1.ts and t3.id=t1.id
        )
    WHERE T1.ID='865'
ORDER BY t1.ts

Ich würde es mit folgenden probieren:

SET @OVAL=0;
SELECT *,t.val,t.val-@OVAL,@oval := t.val FROM `ts_number` t where id = 865 order by ts limit 20

id ist der Datenpunkt (SELECT id FROM iobroker.datapoints WHERE name="viessmann.0.get.VitoLaufzeitSolar")

Er rechnet einfach die aktuelle Zeile minus die letzte.

Where musst du natürlich anpassen. Limit ist nur für schnelleres Testen.

[Mitch]

Hi,

ich lasse mir über einen Graphen immer den Tagesverbrauch angeben:

SELECT
  floor(n.ts/1000) as time_sec,
  n.val as value,
  d.name as metric
FROM ts_number n,datapoints d
WHERE $__unixEpochFrom()*1000<n.ts and/$__unixepochto()*1000="">n.ts and n.id = 8 and n.id=d.id
ORDER BY n.ts ASC</n.ts>

Allerdings ist das immer der Wert von gestern, der geloggt wird. Wie kann ich jetzt immer einen Tag oben im SQL-Statement abziehen, damit der Tag wieder stimmt?

[Dutchman]

Brauchst du nicht, in Grafana hast du Time-Shift in den grafiek Optionen

Sent from my iPhone using Tapatalk

[Mitch]

@Dutchman:

Brauchst du nicht, in Grafana hast du Time-Shift in den grafiek Optionen

Sent from my iPhone using Tapatalk `

Das verschiebt nur die Anzeige, aber die Werte bleiben an den falschen Tagen, also + 1

[andre]

Hallo,
ich mache gerade meine ersten Schritte in Grafana und stehe noch ein wenig mit der SQL-Syntax auf Kriegsfuß. Besser gesagt bekomme ich meinen Graphen nicht "smooth"...
Im Detail geht es um meine PV-Leistung. Ich habe aktuell diese Abfrage:

SELECT
  floor(ts/1000) as time_sec,
  val as value,
  "PV-Leistung" as metric
FROM ts_number
WHERE $__unixEpochFrom()*1000 < ts and $__unixEpochTo()*1000 > ts and id = 2
ORDER BY ts

Funktioniert soweit tadellos. Jetzt versuche ich seit Tagen übers zusammen-googlen mit MEAN bzw. AVG und GROUP BY meine Abfrage zu tunen. Zum einen in Hinblick auf kleinere Datenmengen bei der Abfrage, zum Anderen für eine "smoothe" Grafik...
Hat vielleicht jemand ein Beispiel dazu, welches ich mit meinen SQL-Grundkenntnissen auch verstehen kann? Oder wie müsste meine Abfrage erweitert werden, wenn ich z.B. aus den Werten von jeweils 5 Minuten einen Mittelwert bilden möchte?

Danke.

MfG,
André

[Denis103]

Ich mache grad meine ersten Gehversuche mit Garfana und einer mySQL-DB als Datenquelle. Als Beispiel habe ich hier aus dem Thread meine Test-Abfrage adaptiert:

SELECT
  floor(nb.ts/1000) AS time_sec,
  nb.val AS value,
  dp.name AS metric
FROM ts_number AS nb, datapoints AS dp
WHERE $__unixEpochFrom()*1000 < nb.ts AND $__unixEpochTo()*1000 > nb.ts AND nb.id = 3 AND nb.id = dp.id
ORDER BY nb.ts ASC

Beim ausführen der Abfrage erhalte ich in PHPMYADMIN folgenden Fehler:

#1305 - FUNCTION iobroker.$__unixEpochFrom does not exist 

Füge ich das Query in Grafana ein, dann kommt da kein Fehler, aber der Graph zeigt auch keine Daten an.

Stimmt etwas mit meiner mySQL-Syntax nicht?

[simatec]

Ich muss das Thema hier noch einmal aufwärmen. Ich bin gerade dabei bei mir auf Grafana umzustellen und hänge an folgenden Punkt.
Ich möchte gerne eine Graph erstellen, in dem ich meinen Tagesverbrauch an Strom darstellen kann.
Mein Stromzähler werte ich aus und alle Daten werden bei mir in die mysql Datenbank geschrieben.

Wie bekomme ich diese Graph hin, in der mir Tageweise der Verbrauch angezeigt wird?

Hier ein Beispiel, wie ich mir das gerne bauen will:

Meine Datenpunkt in mysql lese ich wie folgt aus:

SELECT
floor(n.ts/1000) as time_sec,
n.val as value,
"Tagesverbrauch" as metric
FROM ts_number n
WHERE $__unixEpochFrom()*1000<n.ts and $__unixEpochTo()*1000>n.ts and n.id = 18
GROUP BY $__unixEpochGroup(n.ts,$__interval,previous)
ORDER BY n.ts ASC

[Segway]

@simatec

Hallo Ich sowas bei mir laufen. Hier die tägliche PV Produktion - fest eingestellt auf die letzten 2 Wochen:

Das ganze per InfluxDB und Grafana:

[simatec]

@Segway
Ich habe inzwischen auch auf Influx gewechselt und meine Log Daten von mysql auf Influx portiert.

[Segway]

@simatec
Läuft es denn dann ?

[simatec]

@Segway
Ja mit Influx lässt sich das einfacher in Grafana umsetzen

[WernerS]

@sissiwup

Hallo

also das funktioniert bei mir. Ich benutze Mysql und MariaDB.

SELECT
  floor(nb.ts/1000) AS time_sec,

  nb.val AS value,
  dp.name AS metric
FROM ts_number AS nb, datapoints AS dp
WHERE $__unixEpochFrom()*1000 < nb.ts AND $__unixEpochTo()*1000 > nb.ts AND nb.id = 36 AND nb.id = dp.id
ORDER BY nb.ts ASC

Ich bekomme manchmal den Fehler "Data is missing a Time Field" obwohl daten vorhanden sind

Gruß
Werner

[Benjamin Häublein]

@werners Ich habe es jetzt folgendermaßen gelöst:

SELECT val AS value, ts AS "time" FROM iobroker.ts_number WHERE id = 12 AND $__unixEpochFilter(ts/1000)

Das scheint mir das einfachste zu sein.