Hinweis: Schöne Alternative für Charts/Plots mit Grafana und DBLog (MySQL)

[sn0000py]

Okay danke für die infos

Dann gleich mal zwei fragen

1.) Wenn ich auf diese InfluxDB umsteigen, schaffe ich es da meine alt Daten zu übernehmen?
2.) Wenn das Grafana nicht oder sehr schelcht gewartet wird, ist es dann überhaupt wert da drauf umzusteigen? -So was wie ein eintellbares Datumsformat sollte normalerweise ja kein Problem sein, und für eine ChartSoftware unterster Standard normalerweise?

[sn0000py]

weiss jemand auch wie man mit grafana einen delta graph bekommt?

Ich habe den Verbrauch von einem Gerät, aber das ist halt einfach ein steigender Wert ... da möchte ich gerne auf einen stunden oder viertelstunde verbrauch zurückrechnen

[kadettilac89]

Okay danke für die infos

Dann gleich mal zwei fragen

1.) Wenn ich auf diese InfluxDB umsteigen, schaffe ich es da meine alt Daten zu übernehmen?
2.) Wenn das Grafana nicht oder sehr schelcht gewartet wird, ist es dann überhaupt wert da drauf umzusteigen? -So was wie ein eintellbares Datumsformat sollte normalerweise ja kein Problem sein, und für eine ChartSoftware unterster Standard normalerweise?

1) ich weiß nicht wie fit du bist ... "man" kann es machen ... habe dazu mal ein Script geschrieben und für andere bereiet gestellt ...
https://forum.fhem.de/index.php/topic,71551.msg838940.html#msg838940 ...

2) das ist definitiv nicht der fall .... grafana wird sehr stark und schnell gewartet. für docker gibt es sehr häufig neue releases. deutsches datumsformat hat halt geringe prio
es gibt dinge die auch mir nicht gefallen aber man kann es ja mal testen ...

weiss jemand auch wie man mit grafana einen delta graph bekommt?

Ich habe den Verbrauch von einem Gerät, aber das ist halt einfach ein steigender Wert ... da möchte ich gerne auf einen stunden oder viertelstunde verbrauch zurückrechnen

in sql wird das komplex. versucht mal das statistic oder history modul in fhem oder per userreading und schreibe dir damit minutenwerte ....

[peterk_de]

weiss jemand auch wie man mit grafana einen delta graph bekommt?

Ich habe den Verbrauch von einem Gerät, aber das ist halt einfach ein steigender Wert ... da möchte ich gerne auf einen stunden oder viertelstunde verbrauch zurückrechnen

So in etwa? Sowas ist mit SQL schon ein bissel knifflig, aber geht

Code Auswählen Erweitern

SELECT
  TIMESTAMP(CONCAT(YEAR(MIN(rawValues.ts)), '-', IF(MONTH(MIN(rawValues.ts)) < 10, CONCAT('0',MONTH(MIN(rawValues.ts))), MONTH(MIN(rawValues.ts))), '-01 00:00:00')) AS time,
  SUM(rawValues.valueDiff) *(0.24/1000) as value,
  "Stromverbrauch Luftentfeuchter" as metric
FROM (SELECT
       TIMESTAMP as ts,
       ROUND(if((@old > -1) AND (h1.VALUE - @old) > 0,h1.VALUE - @old, 0),2) AS valueDiff,
       @old := h1.VALUE+0 as lastValue
     FROM history h1, ( SELECT @old:=-1) AS var
     WHERE h1.READING="energy" AND h1.DEVICE = "bg.entfeuchter" AND $__timeFilter(TIMESTAMP)
     ORDER BY ts ASC)
       as rawValues
GROUP BY MONTH(rawValues.ts), YEAR(rawValues.ts)
ORDER BY 1

[sn0000py]

ok super danke, scheint zu funktionieren, wobei ich auch noch stunden langen begutachten keine Ahnung habe was das tun soll

[andies]

Sowas ist mit SQL schon ein bissel knifflig, aber geht

Hast Du einen Link, wo man sich so was mal selber beibringen kann?

[peterk_de]

OK dann mal ein kurzes Tutorial, wie ich so eine Anfrage konstruiere.

Zuerst holt man sich alle Verbrauchswerte im gewünschten Zeitbereich (über die Grafana-Funktion $__timeFilter(TIMESTAMP)), sortiert nach der Zeit:

Code Auswählen Erweitern

SELECT
     TIMESTAMP as ts,
     VALUE as value,
     FROM history h1
     WHERE h1.READING="energy" AND h1.DEVICE = "bg.entfeuchter" AND $__timeFilter(TIMESTAMP)
     ORDER BY ts ASC

Das wäre dann so eine Tabelle:

Code Auswählen Erweitern

ts               |  VALUE
2019-02-11 11:00 |  30002
2019-02-11 11:01 |  30008
2019-02-11 11:02 |  30015
2019-02-11 11:03 |  30027

Allerdings wollen wir ja nicht die jeweiligen absoluten Verbrauchswerte, sondern den Verbrauch in einer bestimmten Zeiteinheit. Um den ausrechnen zu können, erstellen wir stattdessen eine Tabelle, die für jeden Timestamp ausrechnet, wieviel im Vergleich zum letzten Zeitpunkt hinzugekommen ist, also die Differenz zur vorherigen Zeile in der Tabelle:

Code Auswählen Erweitern

SELECT
       TIMESTAMP as ts,
       ROUND(if((@old > -1) AND (h1.VALUE - @old) > 0,h1.VALUE - @old, 0),2) AS valueDiff,
       @old := h1.VALUE+0 as lastValue
     FROM history h1, ( SELECT @old:=-1) AS var
     WHERE h1.READING="energy" AND h1.DEVICE = "bg.entfeuchter" AND $__timeFilter(TIMESTAMP)
     ORDER BY ts ASC

Hier nutze ich eine SQL-Variable "@old". Diese wird mit -1 initialisiert (über SELECT @old:=-1) und enthält dann für jeden Zeile immer den vorherigen Zählerstand (also den Wert aus der Zeile darüber), so dass man über eine einfache Subtraktion h1.VALUE - @old die Differenz zur aktuellen Zeile bilden kann. Das gibt dann (zur Veranschaulichung leicht vereinfacht) so eine Tabelle:

Code Auswählen Erweitern

ts               |  VALUE | @old  | valueDiff
2019-02-11 11:00 |  30002 | -1    | 0*
2019-02-11 11:01 |  30008 | 30002 | 6
2019-02-11 11:02 |  30015 | 30008 | 7
2019-02-11 11:03 |  30027 | 30015 | 12

Die 0 für die erste Zeile besorgt uns dabei das if im SELECT-TEIL: if((@old > -1) ... ,h1.VALUE - @old, 0) Hier wird über AND (h1.VALUE - @old) > 0 auch sichergestellt, dass Sprünge im Zähler (Reset des Energy-Counters auf 0) ignoriert werden.

So, was jetzt noch fehlt ist das Zeitslot-weise Aufsummieren dieser Einzeldifferenzen. Dazu packen wir unsere Abfrage als "Unterabfrage" in einen "Wrapper":

Code Auswählen Erweitern

SELECT
  rawValues.ts AS time,
  SUM(rawValues.valueDiff) as value,
  "Stromverbrauch" as metric
FROM ( .... unsere Tabelle mit den Einzeldifferenzen .... )
       as rawValues
GROUP BY MONTH(rawValues.ts), YEAR(rawValues.ts)

Hierzu bitte bei Bedarf die Aggregationsfunktionen von SQL einmal nachschlagen (Group by / SUM etc).

Bleibt noch, Grafana dazu zu bringen, die Balken mittig in den jeweiligen Zeitraum zu setzen. Dazu wird rawValues.ts AS time, ersetzt durch das etwas sperrige Konstrukt:

Code Auswählen Erweitern

TIMESTAMP(CONCAT(YEAR(MIN(rawValues.ts)), '-', IF(MONTH(MIN(rawValues.ts)) < 10, CONCAT('0',MONTH(MIN(rawValues.ts))), MONTH(MIN(rawValues.ts))), '-01 00:00:00')) AS time,

Und alles zusammen:

Code Auswählen Erweitern

SELECT
  TIMESTAMP(CONCAT(YEAR(MIN(rawValues.ts)), '-', IF(MONTH(MIN(rawValues.ts)) < 10, CONCAT('0',MONTH(MIN(rawValues.ts))), MONTH(MIN(rawValues.ts))), '-01 00:00:00')) AS time,
  SUM(rawValues.valueDiff) *(0.24/1000) as value,
  "Stromverbrauch Luftentfeuchter" as metric
FROM (SELECT
       TIMESTAMP as ts,
       ROUND(if((@old > -1) AND (h1.VALUE - @old) > 0,h1.VALUE - @old, 0),2) AS valueDiff,
       @old := h1.VALUE+0 as lastValue
     FROM history h1, ( SELECT @old:=-1) AS var
     WHERE h1.READING="energy" AND h1.DEVICE = "bg.entfeuchter" AND $__timeFilter(TIMESTAMP)
     ORDER BY ts ASC)
       as rawValues
GROUP BY MONTH(rawValues.ts), YEAR(rawValues.ts)
ORDER BY 1

So ... als Übung kannst du das jetzt auf stundenweise abändern

[sn0000py]

So in etwa? Sowas ist mit SQL schon ein bissel knifflig, aber geht

Code Auswählen Erweitern

SELECT
  TIMESTAMP(CONCAT(YEAR(MIN(rawValues.ts)), '-', IF(MONTH(MIN(rawValues.ts)) < 10, CONCAT('0',MONTH(MIN(rawValues.ts))), MONTH(MIN(rawValues.ts))), '-01 00:00:00')) AS time,
  SUM(rawValues.valueDiff) *(0.24/1000) as value,
  "Stromverbrauch Luftentfeuchter" as metric
FROM (SELECT
       TIMESTAMP as ts,
       ROUND(if((@old > -1) AND (h1.VALUE - @old) > 0,h1.VALUE - @old, 0),2) AS valueDiff,
       @old := h1.VALUE+0 as lastValue
     FROM history h1, ( SELECT @old:=-1) AS var
     WHERE h1.READING="energy" AND h1.DEVICE = "bg.entfeuchter" AND $__timeFilter(TIMESTAMP)
     ORDER BY ts ASC)
       as rawValues
GROUP BY MONTH(rawValues.ts), YEAR(rawValues.ts)
ORDER BY 1

zwei fragen hätte ich, wie kommst du beim SUM(rawValues.valueDiff) *(0.24/1000) as value, auf das *0,24*1000 ?
und zweites wäre es in dem Fall nicht noch performanter im inneren SQL noch mal zuerst nach Jahr/Monat zu gruppieren und den MAX(VALUE) verwenden.

Für den Fall das der Zähler nur aufsteigend ist?

[peterk_de]

Oh das habe ich vergessen zu erläutern:

Das ist die Umrechnung von Wattstunden nach Strompreis

Und ja, performancemäßig ist es tatsächlich besser, erst zu Gruppieren und dann mit Max und Min zu rechnen. Dafür hätte ich aber für jedes Device, wo ich das machen will, ein zusätzliches monotonic Userreading in FHEM erstellen müssen - eben weil sonst Resets des Zählers falsch behandelt werden. Das wollte ich mir sparen bzw. war quasi unmöglich, da ich meine DB schon seit mehreren Jahren befüllt hatte, bevor ich Grafana gefunden habe

Und so lahm ist das wirklich nicht, im Gegenteil, auf meiner NUC ist die Anfrage in wenigen Millisekunden durch bei > 30M Zeilen in der DB und > 100K betroffenen Zeilen pro Anfrage. Wenn das lahm sein sollte, checkt mal die Indizes.

[sn0000py]

hhahahah okay ja im bild hätte man es erkennen können das du da euro hast.... ja dann ist logisch und habs nun verstanden

nein performance mässig passts im moment eh bei mir da gibts nicht zu bemängeln

[tatu123]

Hallo zusammen,

ich versuche mich gerade an Grafana. So weit so gut. Ich habe jetzt schon mehrere Kurven eingerichtet. Was ich nur nicht hin bekomme das die Kurven nicht 1 Stunde versetzt sind und bis zur aktuellen Zeit angezeigt werden. Ich denke das kommt von meiner localtime (CET) und der Grafanatime (UTC). Auch das Einstellen von "local brower time" ändert das nicht.

Ich benutze
Grafana (v 6.6.2) und Mysql (v. 5.7) im docker. Beide haben localtime CET. Im phpmyadmin sehe ich das die Daten mit korrektem Timestamp in der Datenbank sind. Im gplot im fhem sehe ich die Kurve korrekt dargestellt. Im Grafana +1h in der Darstellung mit z.B.

Code Auswählen Erweitern


SELECT
  UNIX_TIMESTAMP(TIMESTAMP) as time_sec,
  CONVERT(VALUE, DECIMAL) as value,
  'Solar-Erzeugung' as metric
FROM `history`
WHERE $__timeFilter(TIMESTAMP) and DEVICE = "shelly1pm_solar" and READING = "relay_0_power"
ORDER BY TIMESTAMP ASC


Jetzt kann ich mit

Code Auswählen Erweitern


SELECT
  (CONVERT_TZ(TIMESTAMP,'+01:00', '+00:00')) as time_sec,
  CONVERT(VALUE, DECIMAL) as value,
  'Solar-Erzeugung' as metric
FROM `history`
WHERE $__timeFilter(TIMESTAMP) and DEVICE = "shelly1pm_solar" and READING = "relay_0_power"
ORDER BY TIMESTAMP ASC


die Kurve in der Zeit richtig "stellen" aber dann fehlt mir am Ende der Kurve die letzte Stunde.

Wie macht ihr das ?

Heiko

[rcmcronny]

Hoi,

bei mir sehen die Abfragen so aus:

Code Auswählen Erweitern


SELECT
UNIX_TIMESTAMP(DATE_FORMAT(TIMESTAMP, '%Y-%m-%d %H:%i:%s')) as time_sec,
    CAST(VALUE AS DECIMAL(10,2)) as value,
    'Wohnzimmer' as 'metric'
  FROM history
  WHERE READING="Wohnzimmer" AND DEVICE="ESPEasy_ESP_1WireGw1" AND $__timeFilter(TIMESTAMP)
  ORDER BY time_sec ASC
[code]

Ronny

[tatu123]

Ja hatte ich auch schon probiert. Hat bei mir auch eine Stunde Zeitverschiebung. Bei Dir nicht ?

[rcmcronny]

Hi,

nö, ist bei mir aktuell, wie es sein soll.

Ronny

[volschin]

Es bringt überhaupt nichts, wenn Du am Select schraubst, Du musst am timestamp der Where Clause anpassen.