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

Begonnen von Thyraz, 08 Oktober 2017, 15:02:38

Vorheriges Thema - Nächstes Thema

Thyraz

Hallo zusammen. :)

Worum gehts:
Grafana beschreibt sich als "The open platform for beautiful analytics and monitoring".
Eine Live Demo zum Erkunden findet man hier: http://play.grafana.org

Grafana konnte bis vor kurzem noch nicht mit MySQL als Datasource umgehen, was das Zusammenspiel mit der eigenen DBLog Datenbank erschwerte.
(Dadurch entstand z.B. das hier im Forum erhältliche Modul zum Loggen in eine InfluxDB, womit man eine doppelte Datenhaltung in Kauf nahm.)
Dies hat sich zum Glück seit einiger Zeit geändert und seit Grafana 4.5.1 ist das Ganze sehr brauchbar.

Was ist der Vorteil der Lösung?
- Man kann den angezeigten Zeitraum einfach über ein Dropdown ändern. Dort gibt es viele Presets wie Letzte 24h, 6 Monate, 1 Jahr, ... und eine Auswahl über eine Kalenderansicht.
Auswertungen machen so weit mehr Freude und gehen leicht von der Hand.

- Die Legende kann außerdem automatisch Dinge wie Min/Max/Durchschnitt aller Werte im gerade gewählten Zeitraum mit anzeigen was auch sehr nett ist.

- Man kann direkt in Grafana Dashboards mit verschiedenen Auswertungen arrangieren. Ein weiteres Frontend zur Platzierung mehrerer Charts wird also nicht benötigt.
Das Embedden einzelner Charts in andere Seiten ist aber natürlich auch möglich.

- Es gibt verschiedene Paneltypen, welche neben dem klassischen Chart (Line / Bar / Point) auch Pie Charts oder Tabellen beinhalten.

- Durch die Nutzung von MySQL Select Requests hat man vielseitige Möglichkeiten Daten auszuwerten oder auch schon beim Laden zu manipulieren oder auszudünnen.
Das geschieht dann direkt serverseitig in MySQL und das Ausdünnen entlastet Clients wie etwa Mobilgeräte und erzeugt dann auch weniger Traffic.

- Die UI lädt schnell und ist auf all meinen Geräten flüssig und einfach zu bedienen.
Sie passt sich auch bei Größenänderungen des Fensters (oder drehen des Mobilgeräts) an und zeichnet die Charts hier sehr schnell neu.
Auch das ist ein deutlicher Vorteil gegenüber den Charts die ich bisher mit FHEM/TabletUI/... genutzt hatte.


Installation und Konfiguration kommt im nächsten Post..
Fhem und MariaDB auf NUC6i5SYH in Proxmox Container (Ubuntu)
Zwave, Conbee II, Hue, Harmony, Solo4k, LaMetric, Echo, Sonos, Roborock S5, Nuki, Prusa Mini, Doorbird, ...

Thyraz

Voraussetzungen:
Ihr braucht eine DBLog Instanz die in eine MySQL Datenbank loggt.
Ich hatte bisher SQLite3 verwendet und habe meine Altdaten daher in eine neue MySQL Datenbank umgezogen.

Installation und Einrichtung einer DBLog Instanz mit MySQL:
https://wiki.fhem.de/wiki/DbLog#Beispiel:_Anlegen_und_Nutzung_einer_Mysql-Datenbank

Sicherung der Altdaten aus SQLite und wieder einspielen in MySQL:
https://wiki.fhem.de/wiki/DbRep_-_Reporting_und_Management_von_DbLog-Datenbankinhalten#Datens.C3.A4tze_.28Devices.29_von_einer_Datenbank_in_eine_andere_umziehen_.28Export.2FImport.29
Das Beispiel sichert nur Logdaten für ein einzelnes Gerät und nicht für alles aus der DB.
Das lässt sich aber problemlos anpassen, indem man das Attribut device in der DBRep Instanz nicht belegt.

Ein Umstieg von Filelog auf DBLog würde ebenfalls mit dem ober verlinkten Erstellen einer DBLog Instanz mit MySQL starten.
Import der Altdaten aus den Filelogs wäre darüber möglich:
https://forum.fhem.de/index.php/topic,66383.0.html

Installation
Offizieller Download: https://grafana.com/grafana/download
Fertiges Package für den Raspberry Pi, welches einfach mit dpkg -i grafana_X.X.X_armhf.deb installiert werden kann: https://github.com/fg2it/grafana-on-raspberry/releases

Damit Grafana automatisch mit dem System startet muss der Grafana Server noch aktiviert werden.
Bei Verwendung von Systemd wäre das mit folgendem Befehl zu erledigen: sudo /bin/systemctl enable grafana-server

Grafana lauscht per default auf Port 3000.
Dies kann ein Problem sein, falls man Alexa mit Custom Skill nutzt. Dieser Dienst belegt ebenfalls schon Port 3000.
In dem Fall mit sudo nano /etc/grafana/grafana.ini die Konfiguration anpassen folgende Zeile anpassen:
;http_port = 3000
Hier den führenden Semicolon entfernen den Port entsprechend abändern.

Im Grafana Webinterface dann eine neue Datasource vom Typ MySQL anlegen und die Daten eurer FHEM MYSQL Datenbank eintragen (Port/User/Passwort).
Danach solltest ihr startklar sein.  8)

Hinweise zu MySQL Selects in Grafana
Datenstruktur:
An sich ist es egal wie der MySQL Select aussieht. Grafana interessiert nur, dass es 3 Spalten mit der richtigen Benennung geliefert bekommmt.
  - time_sec muss Zeitwerte im UNIX-Timestamp Format (Sekunden seit 01.01.1970) enthalten. Meist der TIMESTAMP Eintrag aus der Datenbank.
  - value muss Werte für die Y-Achse liefern, welche den Zeitstempeln aus time_sec zuzuordnen sind. Meist der VALUE Eintrag aus der Datenbank.
  - metric ist der Text der für die Kurve in der Legende angezeigt wird. Am Besten z.B. den zugehörigen DEVICE Eintrag aus der DB zurückliefern, oder einen festen String.

Beim den VALUE Einträgen von FHEM muss man beachten, dass diese (da sie auch Text beinhalten können) nicht numerisch sind.
Soll schon intern im SELECT eine Berechnung durchgeführt werden wie AVG oder MIN/MAX dann muss man hier CONVERT(VALUE, DOUBLE) oder CONVERT(VALUE, INTEGER) verwenden. Ansonsten wundert man sich evtl, dass 9 größer 12 ist. ;)

Beispiel:

SELECT
    UNIX_TIMESTAMP(TIMESTAMP) as time_sec,
    VALUE as value,
    "Kurvenbezeichnung" as metric
  FROM history
  WHERE READING="state" AND DEVICE="MyDummy" AND $__timeFilter(TIMESTAMP)


Nützliche Macros:
Grafana bietet ein paar Macros die man in den MySQL Selects verwenden kann, um die zu ladenden Daten auf den angezeigten Zeitraum zu begrenzen.
  - $__timeFilter(TIMESTAMP) liefert eine Überprüfung auf einen Zeitraum zurück, die man in der WHERE Bedingung verwenden kann (siehe obiges Beispiel)
  - $__timeFrom() liefert den Startzeitpunkt des angezeigten Zeitraums als "YYYY-MM-DD HH:MM:SS" zurück.
  - $__timeTo() liefert den Endzeitpunkt des angezeigten Zeitraums als "YYYY-MM-DD HH:MM:SS" zurück (so loggt auch DBLog in die Datenbank).
  - $__unixEpochFrom() liefert den Startzeitpunkt des angezeigten Zeitraums als UNIX-Timestamp Format (Sekunden seit 01.01.1970) zurück.
  - $__unixEpochTo liefert den Endzeitpunkt des angezeigten Zeitraums als UNIX-Timestamp Format (Sekunden seit 01.01.1970) zurück.
Fhem und MariaDB auf NUC6i5SYH in Proxmox Container (Ubuntu)
Zwave, Conbee II, Hue, Harmony, Solo4k, LaMetric, Echo, Sonos, Roborock S5, Nuki, Prusa Mini, Doorbird, ...

Thyraz

Beispiel: Line-Chart für Temperatursensor

Erstellt ein neues Dashboard und dort eine neues Panel vom Typ Chart.
Klickt auf den Panel-Title und im Popup Menu auf Edit.

Wählt hier den Tab "Metrics" um die MySQL Definition anzupassen.
Ersetzt den Code mit diesem hier:


SELECT
  UNIX_TIMESTAMP(TIMESTAMP) as time_sec,
  VALUE as value,
  "Garten" as metric
FROM history
WHERE READING="temperature" AND DEVICE="Thermometer02" AND $__timeFilter(TIMESTAMP)


Danach könnt ihr auf Add Query klicken falls ihr weitere Kurven zu dem Diagramm hinzufügen wollt (weitere Temperatursensoren).

Zu guter Letzt könnte man noch im Reiter General den Chart Title anpassen, im Reiter Axes die Unit auf "°C" setzen und im Reiter Legend entscheiden ob ihr Min/Max/Durchschnittswerte unter dem Diagramm wollt.
Fhem und MariaDB auf NUC6i5SYH in Proxmox Container (Ubuntu)
Zwave, Conbee II, Hue, Harmony, Solo4k, LaMetric, Echo, Sonos, Roborock S5, Nuki, Prusa Mini, Doorbird, ...

Thyraz

Beispiel: Bar Chart mit monatlichen Verbräuchen
Ich habe in meiner Datenbank Logeinträge von mehreren Statistic-Modul Instanzen die mir den täglichen / monatlichen / jährlichen Stromverbrauch diverser Geräte aufzeichnen.

Das Problem ist hier, dass zB. bei den monatlichen Verbräuchen der Timestamp 5 Sekunden vor dem Monatswechsel geloggt wird.
Dadurch sitzt der Balken im Diagramm mehr im nächsten Monat als im eigentlich zugehörigen.
Um das zu umgehen wird die DATE_FORMAT Funktion von MySQL genutzt um Uhrzeit und Tag des Monats im Timestamp zu manipulieren:


SELECT
  UNIX_TIMESTAMP(DATE_FORMAT(TIMESTAMP, '%Y-%m-01')) as time_sec,
  VALUE as value,
  "Trockner" as metric
FROM history
WHERE READING="statEnergyValMonthLast" AND DEVICE="Dummy_PowerSwitch04" AND $__timeFilter(TIMESTAMP)


- Im Reiter Display ist der Draw Mode statt Line auf Bars gesetzt
- Im Reiter Time Range habe ich Override relative time auf 1 Jahr (1y) gesetzt
- Beim Select Statement für das zweite Gerät im Balkendiagramm habe ich den Tag in der DATE_FORMAT Funktion nicht auf 1, sondern auf 4 gesetzt. Dadurch sind die Balken etwas versetzt zu einander.
Das ist optisch von Vorteil falls der hintere Balken mal kleiner ist als vordere.
Fhem und MariaDB auf NUC6i5SYH in Proxmox Container (Ubuntu)
Zwave, Conbee II, Hue, Harmony, Solo4k, LaMetric, Echo, Sonos, Roborock S5, Nuki, Prusa Mini, Doorbird, ...

Thyraz

Beispiel: Mehrere Linecharts stapeln
Um mehrere Schaltsignale (in diesem Fall Ventile der Fußbodenheizung) in einem Chart dazustellen, habe ich den Y-Wert (0 oder 1) der einzelnen Kurven im Select Statement mit unterschiedlichen Offsets verrechnet.
Dadurch werden die Linien untereinander gezeichnet und sind besser ablesbar.

Erste Linie ohne Offset:

SELECT
  UNIX_TIMESTAMP(TIMESTAMP) as time_sec,
  CONVERT(VALUE, INTEGER) + 2 as value,
  "Flur" as metric
FROM history
WHERE READING="Heizkreis_Flur" AND DEVICE="Heizung" AND $__timeFilter(TIMESTAMP)


Zweite Linie mit Offset:

SELECT
  UNIX_TIMESTAMP(TIMESTAMP) as time_sec,
  CONVERT(VALUE, INTEGER) + 2 as value,
  "Wohnzimmer" as metric
FROM history
WHERE READING="Heizkreis_Wohnzimmer" AND DEVICE="Heizung" AND $__timeFilter(TIMESTAMP)


Die weiteren Linien dann eben mit entsprechend größeren Offsets.
Die Achsenbeschriftung der Y-Achse habe ich bei dem Diagramm dann ausgeblendet, da sie keine sinnvolle Information liefert.
Fhem und MariaDB auf NUC6i5SYH in Proxmox Container (Ubuntu)
Zwave, Conbee II, Hue, Harmony, Solo4k, LaMetric, Echo, Sonos, Roborock S5, Nuki, Prusa Mini, Doorbird, ...

Thyraz

Beispiel: Kurve ausdünnen bei zu vielen Messwerten (lange Ladezeiten / langsamer Seitenaufbau)

Meine LaCrosse Thermometer loggen die Temperatur mit 0.1°C Genauigkeit, wodurch man an sich ständig Schwankungen hat und somit massig Messwerte.
Damit die Diagramme auf dem Handy anständig laden, zeige ich weit weniger Punkte in der Kurve an, als Messwerte vorhanden sind.
Um dennoch eine möglichst genaue Darstellung der Kurven zu bekommen, wird dazu jeweils aus den zusammengefassten Punkten der Mittelwert gebildet.

Zuerst das normale Select Statement mit allen Messpunkten:

SELECT
  UNIX_TIMESTAMP(TIMESTAMP) as time_sec,
  VALUE as value,
  "Original" as metric
FROM history
WHERE READING="temperatureVal" AND DEVICE="Thermometer02" AND $__timeFilter(TIMESTAMP)


Und hier dann die angepasste Version mit Gruppierung zu 300s / 5min und der Average Berechnung:

SELECT
  UNIX_TIMESTAMP(TIMESTAMP) as time_sec,
  AVG(CONVERT(VALUE, DOUBLE)) as value,
  Geglättet" as metric
FROM history
WHERE READING="temperatureVal" AND DEVICE="Thermometer02" AND $__timeFilter(TIMESTAMP)
GROUP BY UNIX_TIMESTAMP(TIMESTAMP) DIV 300


Edit:
Es gibt noch eine erweiterte Möglichkeit um die Stärke der Glättung an den gerade gewählten Zeitraum anzupassen.
Das hilft, damit bei einem Anzeigezeitraum von 1 Woche ähnlich viele Datenpunkte angezeigt werden wie wenn man 1 Jahr wählt.
Bei einer Gruppierung mit einem festen Sekunden-Teiler wäre dies nicht der Fall.

Grafana bietet hierzu eine Variable $__interval welche sich beim ändern des Zeitraums anpasst.
Sie nimmt dabei Werte wie z.B. 2s, 5m, 1h an.
Dies kann man wie folgt verwenden:

SELECT
  UNIX_TIMESTAMP(TIMESTAMP) as time_sec,
  AVG(CONVERT(VALUE, DOUBLE)) as value,
  "Geglättet" as metric
FROM history
WHERE READING="temperatureVal" AND DEVICE="Thermometer02" AND $__timeFilter(TIMESTAMP)
GROUP BY UNIX_TIMESTAMP(TIMESTAMP) DIV CASE RIGHT($__interval, 1) 
                                          WHEN 's' THEN $__interval
                                          WHEN 'm' THEN $__interval * 60
                                          WHEN 'h' THEN $__interval * 3600
                                          WHEN 'h' THEN $__interval * 86400
                                          ELSE $__interval
                                        END


Wem die Glättung dabei zu gering ausfällt, kann hinter das *60, *3600 usw. auch noch einen weiteren Multiplikator hinzufügen.
Fhem und MariaDB auf NUC6i5SYH in Proxmox Container (Ubuntu)
Zwave, Conbee II, Hue, Harmony, Solo4k, LaMetric, Echo, Sonos, Roborock S5, Nuki, Prusa Mini, Doorbird, ...

Jupdidu

Hallo,
das liest sich sehr gut.

Könnte man damit auch Charts als PNG exportieren und dann z.B. per Telegram versenden?

Gruß
Jupp

Hauswart

Sieht der gut aus, würde es leider im FHEMWEB / TabletUI nutzen können. Denn eine neue Übersicht nur für Charts sehe ich bei mir nicht, wenn dann TabletUI, damit ich auch einzelne Aktoren steuern kann.
1. Installation:
KNX, Tasmota (KNX), Sonos, Unifi

2. Installation:
HM-CFG-USB, Unifi (, SIGNALduino 868, MySensors, SIGNALduino 433)

Thyraz

Zitat von: Jupdidu am 11 Oktober 2017, 09:50:15
Könnte man damit auch Charts als PNG exportieren und dann z.B. per Telegram versenden?

Ja, gibt in der Doku eine Sektion zum Sharen.
Das beinhaltet verschiedene Embed Lösungen, aber auch direkte PNG Erstellung eines Diagramms.

Hier ein Beispiel mit Link auf die Online-Demo von Grafana:
http://play.grafana.org/render/dashboard-solo/db/grafana-play-home?orgId=1&panelId=4&from=1507705926296&to=1507713126297&width=450&height=200&tz=UTC%2B02%3A00&timeout=5000

Zitat von: Hauswart am 11 Oktober 2017, 09:56:48
Sieht der gut aus, würde es leider im FHEMWEB / TabletUI nutzen können. Denn eine neue Übersicht nur für Charts sehe ich bei mir nicht, wenn dann TabletUI, damit ich auch einzelne Aktoren steuern kann.

Für TabletUI könnten die Embed Möglichkeiten hilfreich sein:
http://docs.grafana.org/reference/sharing/
Ich weiß nur nicht wie das dann mit der Wahl des Zeitraums ist, wenn man keine Topbar von Grafana hat.
Müsste man mal testen.

Ich hab mir eher überlegt die gesamte Grafana Seite in einen Pagetab von TabletUI per iframe zu legen. Mal sehen...
Evtl. bekommt man das mit ein paar CSS Anpassungen auch optisch stimmig zum Rest hin.

Edit: der iframe Link beinhaltete die Timerange.
Man könnte mit etwas Javascript sicher den Wert eine Dropdowns in TabletUI verwenden und das iframe neu Laden.
Fhem und MariaDB auf NUC6i5SYH in Proxmox Container (Ubuntu)
Zwave, Conbee II, Hue, Harmony, Solo4k, LaMetric, Echo, Sonos, Roborock S5, Nuki, Prusa Mini, Doorbird, ...

Thyraz

Beispiel: Plottabriss vermeiden

Fast jede Plot-Lösung lädt ja immer nur die Daten für den anzuzeigenden Zeitraum.
Dadurch ergibt sich das Problem, dass die Linie oft nicht zu Beginn des Diagramms anfängt und schon vor dem Ende des Diagramms (oder des aktuellen Zeitstempels) aufhört.

Nun könnte man natürlich FHEM zwingen mehr Werte zu loggen, z.B. in festen Zeitabständen wie jede Minute.
Das erzeugt aber natürlich Unmengen an unnötigen Daten.

Die zweite Lösung ist die, welche normal mit den SVGPlots in FHEM in Verbindung mit Logproxy empfohlen wird:
den Abfragebereich der Werte größer machen als den angezeigten Bereich.
Das wäre mit Grafana und MySQL auch Problemlos möglich.

Das hat aber auch einen Nachteil:
Ändert sich z.B. ein Wert einmal über mehrere Tage nicht, kann es wieder zu einem Abriss kommen obwohl ich ein paar Stunden mehr lade als angezeigt.
Oder ich lade wirklich mehrere Tage vor und nach dem Anzeigebereich.
Wenn die Kurven dann aber doch viele Einträge haben, kann das Laden der Diagramme sehr langsam werden.

Daher hab ich mir überlegt was man eigentlich genau will:
- Den letzte Datenpunkt vor dem angezeigten Zeitraum
- Den ersten Datenpunkt nach dem angezeigten Zeitraum (Bei einem Anzeigezeitraum bis zur aktuellen Zeit, oder bis in die Zukunft wird hier eben kein Datenpunkt gefunden)
- Einen zusätzlichen virtuellen (nicht in MySQL vorhandenen) Datenpunkt, der den letzten Zustand in der Datenbank nimmt und ihn mit der aktuellen Zeit kombiniert.
  Dadurch wird der Plot auch nach dem letzten Wert in der DB noch bis zum aktuellen Zeitpunkt weitergezeichnet.

Das ganze sind dann einzelne Select Statements, deren Ergebnisse mit UNION zu einer Tabelle verbunden werden:

-- Eigentliche Kurve
(
  SELECT
    UNIX_TIMESTAMP(TIMESTAMP) as time_sec,
    VALUE as value,
   "Wohnzimmer" as metric
  FROM history
  WHERE READING = "Heizkreis_Wohnzimmer" AND DEVICE = "Heizung" AND $__timeFilter(TIMESTAMP)
)

UNION

-- Kurve am Anfang um den letzten Wert vor dem aktuellen Zeitraum erweitern
(
  SELECT
    UNIX_TIMESTAMP(TIMESTAMP) as time_sec,
    VALUE as value,
    "Wohnzimmer" as metric
  FROM history
  WHERE READING = "Heizkreis_Wohnzimmer" AND DEVICE = "Heizung" AND TIMESTAMP <= $__timeFrom() -- Letzter Wert vor Anfangszeit des aktuellen Zeitraums
  ORDER BY TIMESTAMP DESC
  LIMIT 1   
)

UNION

-- Kurve mit nächstem Wert nach dem aktuellen Zeitraum erweitern
(
SELECT
    UNIX_TIMESTAMP(TIMESTAMP) as time_sec,
    VALUE as value,
    "Wohnzimmer" as metric
  FROM history
  WHERE READING = "Heizkreis_Wohnzimmer" AND DEVICE = "Heizung" AND TIMESTAMP >= $__timeTo() -- Letzten Wert im aktuellen Zeitraum holen
  ORDER BY TIMESTAMP ASC
  LIMIT 1 
)

UNION

-- Kurve mit letztem Wert der Kurve (nicht des gewählten Zeitraums) bis zum aktuellen Zeitpunkt erweitern
(
SELECT
    UNIX_TIMESTAMP(NOW()) as time_sec,
    VALUE as value,
    "Wohnzimmer" as metric
  FROM history
  WHERE READING = "Heizkreis_Wohnzimmer" AND DEVICE = "Heizung"
  ORDER BY TIMESTAMP DESC
  LIMIT 1
)

ORDER BY time_sec ASC


Da hier nur 3 weitere Werte zusätzlich zur eigentlichen Kurve selektiert werden, spürt man den Unterschied von der Ladezeit nicht.

Im angehängten Screenshot sieht man dann oben die Originalkurve und unten die korrigierte Kurve ohne Plotabriss.
Da der angezeigte Zeitraum in die Zukunft reicht, wird die Kurve bis zum aktuellen Zeitpukt und nicht bis zum Diagrammende gezeichnet.
(Eingestellter Zeitraum war der aktuelle Tag und der Screenshot wurde 12:15 aufgenommen.)
Fhem und MariaDB auf NUC6i5SYH in Proxmox Container (Ubuntu)
Zwave, Conbee II, Hue, Harmony, Solo4k, LaMetric, Echo, Sonos, Roborock S5, Nuki, Prusa Mini, Doorbird, ...

fischit

Zitat von: Thyraz am 11 Oktober 2017, 11:21:48

Für TabletUI könnten die Embed Möglichkeiten hilfreich sein:
http://docs.grafana.org/reference/sharing/
Ich weiß nur nicht wie das dann mit der Wahl des Zeitraums ist, wenn man keine Topbar von Grafana hat.
Müsste man mal testen.

Ich binde mir die Bilder über
http://192.168.2.111:3000/render/dashboard-solo/db/luftdaten?orgId=2&from=now%2Fd&to=now&panelId=1&width=750&height=300&tz=UTC%2B02%3A00
ein.

Den Teil der URL from=now%2Fd&to=now kann man individuell machen.
Standardmäßig sind das fixe Zeitpunkte (wie im Beispiel mit 1507705926296). Mit "Now" etc. kann man das dynamisch gestalten und so einen alternativen, dynamischen Plot im FHEM darstellen.

Beispiel:
- from=now%2Fd&to=now: Today so far
- from=now-30d&to=now: Die letzten 30 Tage
- from=now-395d&to=now-365d: Die letzten 30 Tage vor einem Jahr (Temperaturvergleich zum Vorjahr z.B.)

Grüße
Nils

Thyraz

Hab jetzt unter Hinweise zu MySQL Selects in Grafana in Post 2 mal ein paar Basics zusammengefasst,
was Grafana eigentlich als Rückgabewerte von dem MySQL Select erwartet um das Diagramm zeichnen zu können.

Außerdem die Erklärung wie man dem Select die anzuzeigende Zeitspanne übergibt um nur wirklich benötigten Daten zu laden.

P.S. Ich hoffe ich störe hier keinen mit meinem Monolog. ;D
Aber ich dachte wenn ich mir das als MySQL Noob schon alles hart ergooglen erarbeiten muss,
kann ich es ja wenigstens für die Nachwelt festhalten...

edit: Danke für die Info fischit. :)
Gerade gesehen, dass man die Parameter dafür in der URL eines Dashboards sieht, wenn man den zeitraum umstellt.
Fhem und MariaDB auf NUC6i5SYH in Proxmox Container (Ubuntu)
Zwave, Conbee II, Hue, Harmony, Solo4k, LaMetric, Echo, Sonos, Roborock S5, Nuki, Prusa Mini, Doorbird, ...

Thyraz

Beispiel: Bereich zwischen 2 Kurven füllen statt bis zur Nulllinie

Ein Feature, das mit anderen Charting Lösungen in FHEM meines Wissens nach bisher nicht ging (man korrigiere mich gerne ;)).
Ich habe hier ein Temperaturchart, welches über das ganze Jahr hinweg die Tagesmitteltemperatur anzeigt.

Da diese allein aber noch nicht so wirklich aussagekräftig ist, wird dahinter noch der Bereich zwischen Min/Max Temperatur des Tages angezeigt.
Gerade die letzten Tage haben wir ja eine Hohe Max-Temperatur aber durch die kalten Nächte dennoch keine wirklich hohen Durchschnittstemperaturen.

Dazu habe ich 3 Kurven angelegt.

Tagesmitteltemperatur:

SELECT
  UNIX_TIMESTAMP((TIMESTAMP) as time_sec,
  AVG(CONVERT(VALUE, double)) as value,
  "Tagesdurchschnitt" as metric
FROM (SELECT TIMESTAMP, VALUE
FROM history
WHERE READING="temperatureVal" AND DEVICE="Thermometer02" AND $__timeFilter(TIMESTAMP)
GROUP BY DATE_FORMAT(TIMESTAMP, '%Y-%m-%d %H')
ORDER BY TIMESTAMP ) h
GROUP BY UNIX_TIMESTAMP(DATE_FORMAT(TIMESTAMP, '%Y-%m-%d'))

Hier werden erst nach Zeit sortiert alle Temperaturwerte eingelesen und nach Stunden gruppiert. Dadurch wird jeweils der erste Wert pro Stunde selektiert.
Diese Werte werden dann nochmal nach Tagen gruppiert, und aus allen Einzelwerten einer Tagesgruppe wird der Durchschnitt gebildet.

Damit versuche ich möglichst nahe an die Definition aus Wikipedia zu kommen, nach der aus allen Stundenwerten von 00 bis 23 Uhr die Tagesmitteltemperatur gebildet wird:
https://de.wikipedia.org/wiki/Tagesmitteltemperatur

Tages-Maxwerte:

SELECT
  UNIX_TIMESTAMP(TIMESTAMP) as time_sec,
  MAX(CONVERT(VALUE, double)) as value,
  "Min/Max" as metric
FROM history
WHERE READING="temperatureVal" AND DEVICE="Thermometer02" AND $__timeFilter(TIMESTAMP)
GROUP BY UNIX_TIMESTAMP(DATE_FORMAT(TIMESTAMP, '%Y-%m-%d'))


Tages-Minwerte:

SELECT
  UNIX_TIMESTAMP(TIMESTAMP) as time_sec,
  MIN(CONVERT(VALUE, double)) as value,
  "Min/Max Baseline" as metric
FROM history
WHERE READING="temperatureVal" AND DEVICE="Thermometer02" AND $__timeFilter(TIMESTAMP)
GROUP BY UNIX_TIMESTAMP(DATE_FORMAT(TIMESTAMP, '%Y-%m-%d'))


Unter Display -> Series Overrides wird nun folgendes eingestellt:
- Kurve Min/Max -> Fill below to : Min/Max BaseLine (Sollte automatisch Lines: False für beide Linien als Override hinzufügen, so dass nur die Füllung und keine Linien zu sehen sind
- Min/Max Baseline -> Legend: false, damit man als Legende nur Tagesmittelwert und Min/Max sieht.

Als festen Anzeigezeitraum für das Diagram habe ich dann noch 1 Jahr eingestellt.

Für den Screenshot hab ich jetzt aber kurz auf 1 Monat umgestellt, da wir erst umgezogen sind und das Diagramm sonst so leer aussieht.
Fhem und MariaDB auf NUC6i5SYH in Proxmox Container (Ubuntu)
Zwave, Conbee II, Hue, Harmony, Solo4k, LaMetric, Echo, Sonos, Roborock S5, Nuki, Prusa Mini, Doorbird, ...

fischit

Hi Thyraz,

ich finds sehr geil was du hier ausarbeitest und als fertige Lösung zur Verfügung stellst.
Ich hab selber sehr wenig Ahnung von SQL aber damit kann ich endlich die Daten in einer schönen Weise grafisch darstellen.

Auch kommen so gerade neue Ideen und Dinge die ich umsetzen will :)

Von daher führ gerne deinen "Monolog" weiter - ich lese fleißig mit.

Grüße

sku

Hallo

zu diesem Punkt
Zitat von: Thyraz am 11 Oktober 2017, 07:33:04
Beispiel: Kurve ausdünnen bei zu vielen Messwerten (lange Ladezeiten / langsamer Seitenaufbau)

hätte ich eine Idee, welche ich leider nicht umsetzen kann:
Wäre es möglich, nur Messpunkte nach einer Wertänderung anzuzeigen, anstatt einen Durchschnitt zu berechnen?
Hintergrund ist, wenn ich mein Raumklima übers Jahr darstellen will, werden die rechtecke der Ventilöffnungen komplett verfälscht.

Ich möchte DBLog nicht so umstellen, dass nur geänderte Werte geloggt werden.


Grüße