Grafana-Abfragen von iobroker MySQL

[sissiwup]

Hallo,

hast du mal in deiner Abfrage

CONVERT(n.val,UNSIGNED INTEGER) AS value

probiert (fals es integer sind, ansonsten anderen Datentyp verwenden).

[Dutchman]

@sissiwup:

Hallo,

hast du mal in deiner Abfrage

CONVERT(n.val,UNSIGNED INTEGER) AS value

probiert (fals es integer sind, ansonsten anderen Datentyp verwenden). `

Du bist mein held des Tages ich danke dir !!!!!!!

` > SELECT

floor(n.ts/1000) as time_sec,

CONVERT(n.val,UNSIGNED INTEGER) AS value,

"Datenpunkt" as metric

FROM ts_string n

WHERE $__unixEpochFrom()*1000<n.ts and/$__unixepochto()*1000="">n.ts and n.id = 31

ORDER BY n.ts ASC</n.ts> `

jetzt bleibt mir noch eine frage offen hoffe da könnt ihr mir auch mit helfen.

Man muss ja jetzt für jeden wert in de Grafik einen eigenen SQL query schreiben mit der dazugehörigen ID.

Oben wird genannt das man Variablen/datanpunkte definieren kann sodas man diese auswählen könnte ich blick da aber nicht ganz durch.

Ich würde halt gerne erreichen das ich nicht jedesmal den query ändern muss, sondern die datenpunkte der DB selektieren kann gibt es dafür eine Möglichkeit ?

cheers und danke soweit !

~Dutch

[Dutchman]

Könntet ihr mir bitte nochmal auf die Sprünge helfen mit einem query der 2 werte miteinander aufzählt und das Resultat als Linie darstellt ?

Hintergrund : Ich habe stroommessungen die einen tage/nacht wert und Tarif kennen, im moment bilde ich diese werte in grafana ab:

mit den folgende SQL statements:

Wert 1:

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

Wert 2:

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

ich würde aber gerne eine Linie haben der wert 1 + wert 2 zeichnet anstatt 2 unterschiedlichen Linien (also das total der beiden werte.

bin für jeden tip sehr dankbar!

[sissiwup]

Hi,

hast du schonmal in grafana -> Display -> stack probiert. Das addiert für dich die Kurven.

Ansosnten ist das Problem bei deinem Wunsch, das die beiden Sensoren nicht immer zur gleichen Zeit Werte liefern.

[Dutchman]

@sissiwup:

Ansosnten ist das Problem bei deinem Wunsch, das die beiden Sensoren nicht immer zur gleichen Zeit Werte liefern. `

Ja nur das klappt in meiner Situation nicht da ich eine Grafik mit Strom Tag/nach und Gas habe (Mal als Beispiel)

Strom muss addiert werden, Gas nicht.

Die Zeiten sind gleich werden durch den Smartmeter Adapter alle 10sekunden übertragen.

Edit: hast Recht Zeiten sind nicht gleich wegen dem "nur loggen bei Änderungen"

Hmm Dan muss ich Gas halt Weck lassen und eigene grafirl bauen.

Problem aber auch das er Linien nicht durchzeichnet. Wen kein Wert da ist, kan man lösen durch alle x min den selben Wert zu loggen vermüllt aber unnötig die DB

~Dutch

–-----------------------

Send from mobile device

Das schöne ios hat Auto Korrektur zum k****

Wer Schreibfehler findet darf sie behalten oder auf eBay verkaufen, mindest Umsatz 10% für die community

[sissiwup]

Unter Display kannst du sagen ob null-Werte als null oder als Verbunden gehandelt werden.

Ansonsten kannst du unter Display Override dein Gas aus dem Stacking rausnehmen und z.B. auch der rechten Y-Achse zuordnen.

[Dutchman]

@sissiwup:

Unter Display kannst du sagen ob null-Werte als null oder als Verbunden gehandelt werden.

Ansonsten kannst du unter Display Override dein Gas aus dem Stacking rausnehmen und z.B. auch der rechten Y-Achse zuordnen. `

Das Stacking gilt leider für die gesamte Grafik, y-Achse links und rechts werden hierbei nicht separiert womit Gas & Strom doch wieder in der Grafik zusammen aufgezählt werden.

Nachteil vom stacking is auch das die Linie zwar aufeinander addiert werden aber kein total wert angezeigt wird (aus durch schaut nach der achse)

NULL werte connected hab ich an, diese Einstellung verbindet aber nur NULL werten zischen datenpunkten und nicht wen am ende einer fehlt.

Ich habe das jetzt gelöst durch alle 5 Minuten die werte in die DB zu schreiben auch wen diese sich nicht geändert haben diese Lösung finde ich aber nicht schön (unnötige dauen in der DB)

~Dutch

[sissiwup]

Hallo,

Series Override ist hier das Zauberwort:

Du siehst hier, alle Werte sind gestackt, nur der my-sql Wert nicht.

Und hier machst du auch die Zuordnung zur rechten Y-Achse.

Du kannst hier nahezu alle Default-Werte für jede Kurve überschreiben.

Beim Hover kannst du angeben, das er die Werte addiert, d.h. wenn du mit der Maus

über die Grafik fährst, siehts du eine Summe und nicht nur die Einzelwerte.

[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~~~~

[Dutchman]

Mal als Beispiel, das ergibt jetzt auch solche lustige Sachen…

Aus holländisch übersetzt, dal = Nacht/Wochenende, Piek = Tag.

Jetzt sieht es in de Grafik aber so aus als hätte ich am Wochenende Tag Strom benutzt was natürlich nicht stimmt. An den Werten sieht man es dan aber die Linie ist lustig so.

Daher hätte ich lieber einen total Wert separat.

~~<link_text text="https://uploads.tapatalk-cdn.com/201804 ... e4d7f4.jpg">https://uploads.tapatalk-cdn.com/20180422/1e6895ebdc50fd82a976531807e4d7f4.jpg</link_text>" />

Verzonden vanaf mijn iPad met Tapatalk~~

[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: