Autor Thema: Modul 93_DbRep - Reporting und Management von Datenbankinhalten (DbLog)  (Gelesen 125464 mal)

Online DS_Starter

  • Developer
  • Hero Member
  • ****
  • Beiträge: 5648
Hallo Andreas,

hast recht, hat sich ein wenig aufgeplustert.
Die Datenrettung ist ja de facto abgeschlossen. Sind jetzt ein paar Feinheiten.
@Dieter, wenn noch Bedarf besteht können wir das auch per PM erledigen.
Dann stören wir niemenden hier.  ;)

Grüße,
Heiko
ESXi 6.5 @NUC6i5SYH mit FHEM auf Debian 10, DbLog/DbRep mit MariaDB auf Synology 415+
Maintainer: SSCam, SSChatBot, SSCal, DbLog/DbRep, Log2Syslog, SMAPortal, Watches, Dashboard
Kaffeekasse: https://www.paypal.me/HMaaz
Contrib: https://svn.fhem.de/trac/browser/trunk/fhem/contrib/DS_Starter
Zustimmung Zustimmung x 1 Liste anzeigen

Offline ch.eick

  • Sr. Member
  • ****
  • Beiträge: 787
Hallo Heiko,

ich habe da mal wieder etwas, was unter customized query passen wuerde

Das ganze spuckt den ersten und den letzten Wert in einem Zeitraum, aus der DB raus.
Ich verwende das, wenn ich z.B. einen Strom Zaehler habe und den Verbrauch in dem Zeitraum ermitteln moechte.

SET @begin_time='2020-06-02 12:30:00';SET @end_time='2020-06-02 18:00:00';

SET @device='shelly02';SET @reading='energy_0';

SELECT TIMESTAMP,READING,round(VALUE/1000,0) AS VALUE
   FROM (
     (SELECT TIMESTAMP,READING,VALUE FROM history WHERE DEVICE = @device  AND  READING = @reading AND TIMESTAMP >= @begin_time and TIMESTAMP <= @end_time ORDER BY TIMESTAMP LIMIT 1)
     UNION ALL 
     (SELECT TIMESTAMP,READING,VALUE FROM history WHERE DEVICE = @device AND READING = @reading AND TIMESTAMP >= @begin_time and TIMESTAMP <= @end_time ORDER BY TIMESTAMP DESC LIMIT 1)
  ) AS X1;

+---------------------+----------+-------+
| TIMESTAMP           | READING  | VALUE |
+---------------------+----------+-------+
| 2020-06-02 12:31:01 | energy_0 |    69 |
| 2020-06-02 16:31:05 | energy_0 |    73 |
+---------------------+----------+-------+


Oder mit Berechnung der Differenz
Hierbei ist zu beachten, dass die einzel Selects vertauscht wurden, um den TIMESTAMP des ersten Select zu bekommen. Die Subtraktion wurde durch "mal Minus Eins" des kleineren Wertes erreicht.
SET @begin_time='2020-06-02 12:30:00';SET @end_time='2020-06-02 18:00:00';

SET @device='shelly02';SET @reading='energy_0';

SELECT TIMESTAMP,READING,round(sum(VALUE)/1000,0) AS VALUE
   FROM (
     (SELECT TIMESTAMP,READING,VALUE FROM history WHERE DEVICE = @device AND READING = @reading AND TIMESTAMP >= @begin_time and TIMESTAMP <= @end_time ORDER BY TIMESTAMP DESC LIMIT 1)
     UNION ALL 
     (SELECT TIMESTAMP,READING,(VALUE * -1) AS VALUE FROM history WHERE DEVICE = @device  AND  READING = @reading AND TIMESTAMP >= @begin_time and TIMESTAMP <= @end_time ORDER BY TIMESTAMP LIMIT 1)
  ) AS X1;

+---------------------+----------+-------+
| TIMESTAMP           | READING  | VALUE |
+---------------------+----------+-------+
| 2020-06-02 16:31:05 | energy_0 |     4 |
+---------------------+----------+-------+

Die Formatierung sollte eventuell weg gelassen werden, da diese natuerlich zum zu erwartenden Ergebnis passen muss.
round(sum(VALUE)/1000,0) AS VALUE

Gruss
     Christian
« Letzte Änderung: 02 Juni 2020, 17:01:53 von ch.eick »
RPI4; Docker; CUNX; Eltako FSB61NP; 230V zentral verschaltet; SamsungTV H-Serie; DLNARenderer; TV.pl;  Sonos; Vallox; Luxtronik; 2x FB7490; Stromzähler mit DvLIR; wunderground; clever-tanken; Plenticore 10 mit BYD; EM410; SMAEM; Modbus TCP

Online DS_Starter

  • Developer
  • Hero Member
  • ****
  • Beiträge: 5648
Hallo Christian,

ein sehr schönes Statement, danke !  :)

Ich versuche es als sqlSpecial abzubilden.
Vieleicht wäre es auch eine gute Idee im Wiki bei DbRep eine Rubrik für hilfreiche SQLs aufzumachen ?

Grüße,
Heiko
ESXi 6.5 @NUC6i5SYH mit FHEM auf Debian 10, DbLog/DbRep mit MariaDB auf Synology 415+
Maintainer: SSCam, SSChatBot, SSCal, DbLog/DbRep, Log2Syslog, SMAPortal, Watches, Dashboard
Kaffeekasse: https://www.paypal.me/HMaaz
Contrib: https://svn.fhem.de/trac/browser/trunk/fhem/contrib/DS_Starter
Zustimmung Zustimmung x 2 Liste anzeigen

Offline ch.eick

  • Sr. Member
  • ****
  • Beiträge: 787
Das wäre sicherlich eine gute Idee,  bei meinen abwegigen Einfällen kommt da eventuell einiges zusammen ;-)

So kann mal sich dann Behelfsskripte im FHEM sparen.
« Letzte Änderung: 02 Juni 2020, 20:58:08 von ch.eick »
RPI4; Docker; CUNX; Eltako FSB61NP; 230V zentral verschaltet; SamsungTV H-Serie; DLNARenderer; TV.pl;  Sonos; Vallox; Luxtronik; 2x FB7490; Stromzähler mit DvLIR; wunderground; clever-tanken; Plenticore 10 mit BYD; EM410; SMAEM; Modbus TCP
Zustimmung Zustimmung x 2 Liste anzeigen

Online DS_Starter

  • Developer
  • Hero Member
  • ****
  • Beiträge: 5648
Hallo zusammen,

ich habe im DbRep-Wiki eine Rubrik hilfreiche SQLs angelegt.
Das Beispiel von Christian ist dort schonmal abgelegt.

Fühlt euch bitte eingeladen diesen Teil mit eigenen Ergänzungen zu erweitern. Die Struktur seht ihr ja und jeder kann sich einen Wiki-Benutzer holen um Einträge zu machen.
Vergesst bitte nicht zu erwähnen mit welcher DB (SQLite etc.) ihr eure Statements ausführt, denn nicht jede Syntax passt für alle DB's.
Und auf evtl. Gefahren auch hinweisen.  ;)

Ich werde auch noch ein paar Dinge einfügen aus meinem Fundus.

Grüße,
Heiko
« Letzte Änderung: 05 Juni 2020, 13:50:19 von DS_Starter »
ESXi 6.5 @NUC6i5SYH mit FHEM auf Debian 10, DbLog/DbRep mit MariaDB auf Synology 415+
Maintainer: SSCam, SSChatBot, SSCal, DbLog/DbRep, Log2Syslog, SMAPortal, Watches, Dashboard
Kaffeekasse: https://www.paypal.me/HMaaz
Contrib: https://svn.fhem.de/trac/browser/trunk/fhem/contrib/DS_Starter
Gefällt mir Gefällt mir x 1 Liste anzeigen

Offline ch.eick

  • Sr. Member
  • ****
  • Beiträge: 787
Das Beispiel von Christian ist dort schonmal abgelegt.
Ich war schon fleissig, koenntest Du mal nachschauen, ob es so okay ist?

Bei der sqlSpecial fuer die Werte Differenzen habe ich dort noch eine Korrektur abgelegt. So passt es jetzt besser.
« Letzte Änderung: 05 Juni 2020, 13:36:43 von ch.eick »
RPI4; Docker; CUNX; Eltako FSB61NP; 230V zentral verschaltet; SamsungTV H-Serie; DLNARenderer; TV.pl;  Sonos; Vallox; Luxtronik; 2x FB7490; Stromzähler mit DvLIR; wunderground; clever-tanken; Plenticore 10 mit BYD; EM410; SMAEM; Modbus TCP
Gefällt mir Gefällt mir x 1 Liste anzeigen

Online DS_Starter

  • Developer
  • Hero Member
  • ****
  • Beiträge: 5648
perfekt  :)

Einzige Anmerkung: Die Ergebnistabelle für die Beispiele würde ich auf ein paar Zeilen einkürzen. Die lange Tabelle stört nur und der Nachnutzer hat keinen Mehrwert wenn er _ALLE_ Ergebniszeilen lesen muss.

LG,
Heiko

ESXi 6.5 @NUC6i5SYH mit FHEM auf Debian 10, DbLog/DbRep mit MariaDB auf Synology 415+
Maintainer: SSCam, SSChatBot, SSCal, DbLog/DbRep, Log2Syslog, SMAPortal, Watches, Dashboard
Kaffeekasse: https://www.paypal.me/HMaaz
Contrib: https://svn.fhem.de/trac/browser/trunk/fhem/contrib/DS_Starter

Offline ch.eick

  • Sr. Member
  • ****
  • Beiträge: 787
Einzige Anmerkung: Die Ergebnistabelle für die Beispiele würde ich auf ein paar Zeilen einkürzen. Die lange Tabelle stört nur und der Nachnutzer hat keinen Mehrwert wenn er _ALLE_ Ergebniszeilen lesen muss.
Ich habe es gekuerzt, wenn es noch kuerzer werden soll, dann muss ich auch die Anmerkungen mit der Erklaerung raus nehmen.
RPI4; Docker; CUNX; Eltako FSB61NP; 230V zentral verschaltet; SamsungTV H-Serie; DLNARenderer; TV.pl;  Sonos; Vallox; Luxtronik; 2x FB7490; Stromzähler mit DvLIR; wunderground; clever-tanken; Plenticore 10 mit BYD; EM410; SMAEM; Modbus TCP

Online DS_Starter

  • Developer
  • Hero Member
  • ****
  • Beiträge: 5648
Alles gut, war auch nur eine Empfehlung.  :)
ESXi 6.5 @NUC6i5SYH mit FHEM auf Debian 10, DbLog/DbRep mit MariaDB auf Synology 415+
Maintainer: SSCam, SSChatBot, SSCal, DbLog/DbRep, Log2Syslog, SMAPortal, Watches, Dashboard
Kaffeekasse: https://www.paypal.me/HMaaz
Contrib: https://svn.fhem.de/trac/browser/trunk/fhem/contrib/DS_Starter

Offline flummy1978

  • Full Member
  • ***
  • Beiträge: 393
Maaaaaahlzeit  8)

Ich weiss nicht ob ICH es richtig verstanden habe... aber kleine Anmerkung von mir und für diejenigen die hier nicht den Zusammenhang dieser Befehle gelesen haben:  Ggf wäre es von Vorteil auch das entsprechende DbRep Device auch einmal zu posten bzw ein Beispiel zu bringen....  Wenn der SQL Befehl in FHEM eingesetzt werden soll (und kann) muss man auch verstehen, wie es dort eingesetzt werden kann.

Aber ansosnten kann ich nur loben ... sieht wirklich SUPER aus. Alleine die Übersichtlichkeit in den Befehlen finde ich persönlich MEGA. Durch die Zeilenumbrüche kann man jeden einzelnen Befehl erkennen. Dadurch ist es für Anfänger (oder wenig erfahrene wie mich) leichter den Befehl nachzuvollziehen, wie er zusammen gesetzt wird und daraus lernen. Zu guter Letzt: Wenn man einen Befehlteil nicht kennt, kann man diesen besser rausfiltern und entsprechend danach suchen.... daher: wie immer, alle vorhandenen Daumen hoch  :)

Viele Grüße
Andreas

Offline ch.eick

  • Sr. Member
  • ****
  • Beiträge: 787
Ich weiss nicht ob ICH es richtig verstanden habe... aber kleine Anmerkung von mir und für diejenigen die hier nicht den Zusammenhang dieser Befehle gelesen haben:  Ggf wäre es von Vorteil auch das entsprechende DbRep Device auch einmal zu posten bzw ein Beispiel zu bringen....  Wenn der SQL Befehl in FHEM eingesetzt werden soll (und kann) muss man auch verstehen, wie es dort eingesetzt werden kann.

Aber ansosnten kann ich nur loben ... sieht wirklich SUPER aus. Alleine die Übersichtlichkeit in den Befehlen finde ich persönlich MEGA. Durch die Zeilenumbrüche kann man jeden einzelnen Befehl erkennen. Dadurch ist es für Anfänger (oder wenig erfahrene wie mich) leichter den Befehl nachzuvollziehen, wie er zusammen gesetzt wird und daraus lernen. Zu guter Letzt: Wenn man einen Befehlteil nicht kennt, kann man diesen besser rausfiltern und entsprechend danach suchen.... daher: wie immer, alle vorhandenen Daumen hoch  :)
Ich bedanke mich dehmuetig fuer das Lob...

Diese SQL Statements verwende ich oft in einem separaten Datenbank Zugriff
fhem@raspberrypi:~$ mysql -h [IP-Adresse] --port 3306 --database fhem -u fhemuser -p
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 42516
Server version: 5.5.60-0+deb7u1 (Debian)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [fhem]>
Das kann nuetzlich sein, wenn man mal schnell etwas Uebersicht haben moechte.
Den Aufruf mit DBRep als FHEM device findet man auch in der Dokumentation.
Heiko war auch so nett und hat einige SQL Statements als "sqlSpecial" bereits mit ins Modul eingebaut. Diese findest Du unter:

set [device] sqlSpecial

readingsDifferenceByTimeDelta waere dann ==> "9.2 Temperaturdifferenz eines Pufferspeichers über die Zeit ermitteln"

Hier fehlt noch die Referenz in der Wiki Page, die ich jetzt noch eintrage.

Gruss
    Christian
« Letzte Änderung: 05 Juni 2020, 16:17:45 von ch.eick »
RPI4; Docker; CUNX; Eltako FSB61NP; 230V zentral verschaltet; SamsungTV H-Serie; DLNARenderer; TV.pl;  Sonos; Vallox; Luxtronik; 2x FB7490; Stromzähler mit DvLIR; wunderground; clever-tanken; Plenticore 10 mit BYD; EM410; SMAEM; Modbus TCP

Online DS_Starter

  • Developer
  • Hero Member
  • ****
  • Beiträge: 5648
Im Start der Rubrik im Wiki hatte ich erwähnt wie man die SQL Statements grundsätzlich mit sqlCmd im DbRep ausführt und das Ergebnisdesign anpassen kann. Das gilt dann ja für alle nachfolgend aufgezeigten Statements.
Man muss sie nur einsetzen. Geht mit copy & paste denn sqlCmd ist ja ein großes Editor-Textfeld und nicht nur eine Zeile.

LG,
Heiko
« Letzte Änderung: 06 Juni 2020, 08:05:14 von DS_Starter »
ESXi 6.5 @NUC6i5SYH mit FHEM auf Debian 10, DbLog/DbRep mit MariaDB auf Synology 415+
Maintainer: SSCam, SSChatBot, SSCal, DbLog/DbRep, Log2Syslog, SMAPortal, Watches, Dashboard
Kaffeekasse: https://www.paypal.me/HMaaz
Contrib: https://svn.fhem.de/trac/browser/trunk/fhem/contrib/DS_Starter

Offline Nighthawk

  • Full Member
  • ***
  • Beiträge: 240
Hallo Heiko,

seit ein Paar Tagen habe ich ein seltsames Phaenomen, die Average Werte werden nicht mehr berechnet, in den Readings steht dann "insufficient values", ich kann aber keine Auffälligkeiten an den Werten finden.
Die Max und Min Werte werden problemlos berechnet.

List:
Internals:
   DATABASE   fhem
   DEF        logdb
   FUUID      5d855dda-f33f-69d4-59da-2c00db9bd37e3d8c
   FVERSION   93_DbRep.pm:v8.40.1-s21965/2020-05-18
   LASTCMD    averageValue writeToDB
   MODEL      Client
   NAME       Report.Wetter
   NOTIFYDEV  global,Report.Wetter
   NR         171
   NTFY_ORDER 50-Report.Wetter
   ROLE       Client
   STATE      done
   TYPE       DbRep
   UTF8       0
   HELPER:
     DBLOGDEVICE logdb
     GRANTS     SELECT,USAGE,UPDATE,INSERT,DELETE
     IDRETRIES  3
     MINTS      2018-10-06 11:45:06
     PACKAGE    main
     SQLHIST   
     VERSION    8.40.1
     CV:
       aggregation day
       aggsec     86400
       destr      2020-06-26
       dsstr      2020-06-01
       epoch_seconds_end 1593187199
       mestr      06
       msstr      06
       testr      23:59:59
       tsstr      00:00:00
       wdadd     
       yestr      2020
       ysstr      2020
     DBREPCOL:
       COLSET     1
       DEVICE     64
       EVENT      512
       READING    64
       TYPE       64
       UNIT       32
       VALUE      128
   Helper:
     DBLOG:
       state:
         logdb:
           TIME       1593222219.24083
           VALUE      done
   OLDREADINGS:
   READINGS:
     2020-06-27 09:43:39   2020-06-01__Luftquali__temperature__AVGDMGWS__2020-06-01 16.1
     2020-06-27 09:43:39   2020-06-02__Luftquali__temperature__AVGDMGWS__2020-06-02 18.4
     2020-06-27 09:43:39   2020-06-03__Luftquali__temperature__AVGDMGWS__2020-06-03 insufficient values
     2020-06-27 09:43:39   2020-06-04__Luftquali__temperature__AVGDMGWS__2020-06-04 16.8
     2020-06-27 09:43:39   2020-06-05__Luftquali__temperature__AVGDMGWS__2020-06-05 19.5
     2020-06-27 09:43:39   2020-06-06__Luftquali__temperature__AVGDMGWS__2020-06-06 21.8
     2020-06-27 09:43:39   2020-06-07__Luftquali__temperature__AVGDMGWS__2020-06-07 25.3
     2020-06-27 09:43:39   2020-06-08__Luftquali__temperature__AVGDMGWS__2020-06-08 28.8
     2020-06-27 09:43:39   2020-06-09__Luftquali__temperature__AVGDMGWS__2020-06-09 24.5
     2020-06-27 09:43:39   2020-06-10__Luftquali__temperature__AVGDMGWS__2020-06-10 21.3
     2020-06-27 09:43:39   2020-06-11__Luftquali__temperature__AVGDMGWS__2020-06-11 21.3
     2020-06-27 09:43:39   2020-06-12__Luftquali__temperature__AVGDMGWS__2020-06-12 25.1
     2020-06-27 09:43:39   2020-06-13__Luftquali__temperature__AVGDMGWS__2020-06-13 24.4
     2020-06-27 09:43:39   2020-06-14__Luftquali__temperature__AVGDMGWS__2020-06-14 20.8
     2020-06-27 09:43:39   2020-06-15__Luftquali__temperature__AVGDMGWS__2020-06-15 19.5
     2020-06-27 09:43:39   2020-06-16__Luftquali__temperature__AVGDMGWS__2020-06-16 24.5
     2020-06-27 09:43:39   2020-06-17__Luftquali__temperature__AVGDMGWS__2020-06-17 25.9
     2020-06-27 09:43:39   2020-06-18__Luftquali__temperature__AVGDMGWS__2020-06-18 22.9
     2020-06-27 09:43:39   2020-06-19__Luftquali__temperature__AVGDMGWS__2020-06-19 23.4
     2020-06-27 09:43:39   2020-06-20__Luftquali__temperature__AVGDMGWS__2020-06-20 insufficient values
     2020-06-27 09:43:39   2020-06-21__Luftquali__temperature__AVGDMGWS__2020-06-21 23.1
     2020-06-27 09:43:39   2020-06-22__Luftquali__temperature__AVGDMGWS__2020-06-22 insufficient values
     2020-06-27 09:43:39   2020-06-23__Luftquali__temperature__AVGDMGWS__2020-06-23 insufficient values
     2020-06-27 09:43:39   2020-06-24__Luftquali__temperature__AVGDMGWS__2020-06-24 insufficient values
     2020-06-27 09:43:39   2020-06-25__Luftquali__temperature__AVGDMGWS__2020-06-25 insufficient values
     2020-06-27 09:43:39   2020-06-26__Luftquali__temperature__AVGDMGWS__2020-06-26 insufficient values
     2020-06-27 09:43:39   background_processing_time 2.2513
     2020-06-27 09:43:39   db_lines_processed 38
     2020-06-27 09:43:39   sql_processing_time 2.2298
     2020-06-27 09:43:39   state           done
Attributes:
   aggregation day
   allowDeletion 1
   averageCalcForm avgDailyMeanGWS
   devStateIcon connected:10px-kreis-gelb .*disconnect:10px-kreis-rot .*done:10px-kreis-gruen
   device     Luftquali
   event-on-update-reading state
   group      DB
   icon       security
   reading    temperature
   room       Datenbank
   showproctime 1
   sortby     33
   timestamp_begin current_month_begin
   timestamp_end previous_day_end
   verbose    5


« Letzte Änderung: 27 Juni 2020, 04:08:07 von Nighthawk »

Online DS_Starter

  • Developer
  • Hero Member
  • ****
  • Beiträge: 5648
Guten Morgen,

du berechnest die Tagesdurchschnitte nach den Richtlinien des deutschen Wetterdienstes.
In der CommandRef zum Attribut averageCalcForm = avgDailyMeanGWS ist der Hinweis enthalten:

berechnet die Tagesmitteltemperatur entsprechend den Vorschriften des deutschen Wetterdienstes (siehe "get <name> versionNotes 2").
Diese Variante verwendet automatisch die Aggregation "day".

Wenn man dem Hinweis folgt und "get <name> versionNotes 2" aufruft, erhält man den Link zu den Regularien des deutschen Wetterdienstes. Dort ist die Vorschrift zur Berechnung der Tagesdurchschnitte enthalten:

Ab dem 01.04.2001 wurde der Standard wie folgt geändert:

  *  Berechnung der Tagesmittel aus 24 Stundenwerten
  *  Wenn mehr als 3 Stundenwerte fehlen -> Berechnung aus den 4 Hauptterminen (00, 06, 12, 18 UTC)
   * Bezugszeit für einen Tag i.d.R. 23:51 UTC des Vortages bis 23:50 UTC

Das heißt also, dass deine verfügbaren Rohdaten eine Berechnung nicht erlauben, weil zu den relevanten Zeiten Daten fehlen. Wenn die erste Bedingung nicht eingehalten werden kann und mehr als 3 Stundenwerte fehlen, werden die Haupttermine verwendet. Wenn nur ein Haupttermin fehlt, kommt "insufficient values".

Ich habe mir jetzt nicht die Mühe gemacht alle Werte zu durchschauen, aber das wäre der Ansatz.

LG,
Heiko
« Letzte Änderung: 27 Juni 2020, 12:08:00 von DS_Starter »
ESXi 6.5 @NUC6i5SYH mit FHEM auf Debian 10, DbLog/DbRep mit MariaDB auf Synology 415+
Maintainer: SSCam, SSChatBot, SSCal, DbLog/DbRep, Log2Syslog, SMAPortal, Watches, Dashboard
Kaffeekasse: https://www.paypal.me/HMaaz
Contrib: https://svn.fhem.de/trac/browser/trunk/fhem/contrib/DS_Starter

Offline Nighthawk

  • Full Member
  • ***
  • Beiträge: 240
Hallo Heiko,

danke für die Info, hab mir die Daten angeschaut, da fehlen einige Daten zu den relevanten Stunden (hatte Probleme mit dem Tempsensor), habe erstmal auf das arithmetische Mittel umgestellt.

Gruß
Alex