Modul 93_DbRep - Reporting und Management von Datenbankinhalten (DbLog)

DS_Starter · 04 September 2020, 11:17:31

ZitatIch laufe so optimiert, da habe ich keine Langläufer

Das ist natürlich dramatisch

Du könntest es auch mit einem "get <> dbValue" und einem aufwändigerem Select testen.

BTW... den getter dbValue halte ich von der Namensgebung her für etwas unglücklich. Ich würde es gern in ~~sqlBlockingCmd~~ sqlCmdBlocking umbenennen.
Das trifft die Verwendung besser. Gibt es Gegenstimmen / Zustimmung ?

DS_Starter · 04 September 2020, 14:48:05

Nun habe ich die neue Version finalisiert.
Der getter dbValue ist umbenannt in sqCmdBlocking. Um die Kompatibilität zu gewährleisten, kann man "dbValue" vorerst in Scripten weiterhin verwenden.
Im Log gibt es dann eine Meldung:

Code Auswählen


2020.09.04 14:29:50.666 1: Rep.CPU - WARNING - the command "dbValue" is deprecated and will be removed soon. Please use "sqlCmdBlocking" instead.

Die ComRef ist auch angepasst und der get-Bereich gleich bereinigt und aufgewertet. Es wird die Online-Hilfe für die getter im FHEMWEB mit angezeigt.

Bitte nochmal testen wer mag.
Wenn mir / euch im Laufe des Tages nichts mehr auffällt, checke ich die neue Version ein. Sie ist dann morgen früh wie gewohnt im Update enthalten.

LG,
Heiko

ch.eick · 22 Oktober 2020, 11:49:27

EDIT: Ich habe schon mal ein SELECT geschrieben, dass alle zu löschenden Einträge ausliest.

Moin Heiko,
ich suche gerade eine Möglichkeit zB "reduceLog" um den letzten Eintrag eines Zeitraums zu behalten

Commandref von reduceLog, aber Bezug auf "(den ersten) pro Stunde"

Code Auswählen


set <name> reduceLog <no>[:<nn>] [average[=day]] [exclude=device1:reading1,device2:reading2,...]

Reduziert historische Datensätze, die älter sind als <no> Tage und (optional) neuer sind als <nn> Tage auf einen Eintrag (den ersten) pro Stunde je Device & Reading.

Hierbei geht es mir um einen Zähler, der den ganzen Tag ansteigt. Um das aufzuräumen können später alle Einträge bis auf den letzten gelöscht werden.
Die nächsten Schritte wären dann beim Monatszähler die jeweiligen Wochen zu behalten und
beim Jahreszähler die jeweiligen Monate.

Code Auswählen


MySQL [fhem]> SELECT TIMESTAMP,DEVICE,READING,VALUE FROM history WHERE DEVICE='PV_Anlage_1_API' And READING='Statistic_GridFeedInDay' AND TIMESTAMP LIKE '2020-10-21%' order BY TIMESTAMP DESC;
+---------------------+-----------------+-------------------------+-------+
| TIMESTAMP           | DEVICE          | READING                 | VALUE |
+---------------------+-----------------+-------------------------+-------+
| 2020-10-21 23:57:00 | PV_Anlage_1_API | Statistic_GridFeedInDay | 39.38 |
| 2020-10-21 22:57:00 | PV_Anlage_1_API | Statistic_GridFeedInDay | 39.38 |
| 2020-10-21 21:57:00 | PV_Anlage_1_API | Statistic_GridFeedInDay | 39.38 |
snip ...
| 2020-10-21 02:57:00 | PV_Anlage_1_API | Statistic_GridFeedInDay | 0.00  |
| 2020-10-21 01:57:00 | PV_Anlage_1_API | Statistic_GridFeedInDay | 0.00  |
| 2020-10-21 00:57:00 | PV_Anlage_1_API | Statistic_GridFeedInDay | 0.00  |
+---------------------+-----------------+-------------------------+-------+
24 rows in set (2.623 sec)

## Und den möchte ich behalten
MySQL [fhem]> SELECT TIMESTAMP,DEVICE,READING,VALUE FROM history WHERE DEVICE='PV_Anlage_1_API' And READING='Statistic_GridFeedInDay' AND TIMESTAMP LIKE '2020-10-21%' order BY TIMESTAMP DESC limit 1;
+---------------------+-----------------+-------------------------+-------+
| TIMESTAMP           | DEVICE          | READING                 | VALUE |
+---------------------+-----------------+-------------------------+-------+
| 2020-10-21 23:57:00 | PV_Anlage_1_API | Statistic_GridFeedInDay | 39.38 |
+---------------------+-----------------+-------------------------+-------+
1 row in set (0.003 sec)

wenn das mit reduceLog nicht klappt, müsste ich wieder special SQLs erstellen und das will ja keiner

Code Auswählen


SET @device = 'PV_Anlage_1_API'; SET @reading = 'Statistic_GridFeedInDay'; SET @startdate = '2020-10-21';
SELECT t1.TIMESTAMP,t1.DEVICE,t1.READING,t1.VALUE
  FROM history t1
  INNER JOIN
    (SELECT TIMESTAMP,DEVICE,READING,VALUE
       FROM history
       WHERE DEVICE    = @device    AND
             READING   = @reading   AND
             TIMESTAMP > @startdate AND
             TIMESTAMP < DATE_ADD(@startdate,INTERVAL 1 DAY)
       ORDER BY TIMESTAMP DESC LIMIT 1
    ) x
  ON  x.TIMESTAMP != t1.TIMESTAMP AND
      x.DEVICE     = t1.DEVICE    AND
      x.READING    = t1.READING
  WHERE t1.DEVICE    = @device    AND
        t1.READING   = @reading   AND
        t1.TIMESTAMP > @startdate AND
        t1.TIMESTAMP < DATE_ADD(@startdate,INTERVAL 1 DAY)
  ORDER BY TIMESTAMP;

## Da kommt jetzt alles raus, das gelöscht werden soll. Somit braucht man das Select nun nur noch in ein DELETE umzuschreiben
+---------------------+-----------------+-------------------------+-------+
| TIMESTAMP           | DEVICE          | READING                 | VALUE |
+---------------------+-----------------+-------------------------+-------+
| 2020-10-21 00:57:00 | PV_Anlage_1_API | Statistic_GridFeedInDay | 0.00  |
| 2020-10-21 01:57:00 | PV_Anlage_1_API | Statistic_GridFeedInDay | 0.00  |
snip...
| 2020-10-21 21:57:00 | PV_Anlage_1_API | Statistic_GridFeedInDay | 39.38 |
| 2020-10-21 22:57:00 | PV_Anlage_1_API | Statistic_GridFeedInDay | 39.38 |
+---------------------+-----------------+-------------------------+-------+
## das ist genau bis '2020-10-21 22:57:00' und somit fehlt nur der Eintrag der bleibt


SELECT TIMESTAMP,DEVICE,READING,VALUE        FROM history        WHERE DEVICE=@device And READING=@reading AND TIMESTAMP LIKE @timelike        ORDER BY TIMESTAMP DESC LIMIT 1;
+---------------------+-----------------+-------------------------+-------+
| TIMESTAMP           | DEVICE          | READING                 | VALUE |
+---------------------+-----------------+-------------------------+-------+
| 2020-10-21 23:57:00 | PV_Anlage_1_API | Statistic_GridFeedInDay | 39.38 |
+---------------------+-----------------+-------------------------+-------+

Gruß
Christian

ch.eick · 22 Oktober 2020, 18:18:31

Und schon habe ich die nächste Frage

Es gibt einen Jahreszähler, den ich gerne in Wochen aufteilen möchte, was auch soweit klappt.
Nun gibt es die vermaledeiten Wochen, die über die Monatsgrenze gehen, oder die am Tag der Ausführung noch nicht abgeschlossen sind.

Nun habe ich einen Lauf mit "timestamp_end previous_month_end" gemacht und es wurde ein Wert für 2020-09-30 erstellt.
Danach kam ein Lauf mit gelöschtem "timestamp_end", auch hier endet die Berechnung nicht am Ende der letzten vollständigen Woche.

1.) Beim zweiten Lauf wurde der Eintrag vom '2020-09-30 19:05:00' nicht gelöscht (auch nicht mit allowDeletion = 1)
2.) Es wäre toll, wenn beim ersten Lauf die Berechnung der letzten angefangenen Woche nicht geschrieben würde.
3.) mal angenommen ich würde das nun jede Woche laufen lassen und das timestamp_begin jeweils auf previous_month_begin setzen,
dann überschreibt es mir auch immer wieder die erste Woche des Laufes, wenn sie nicht vollständig ist.
>>>> auch der Beginn darf nur auf vollständige Wochen zugreifen

EDIT: Das kleinste Problem habe ich jetzt schon beseitigt. Mit "timestamp_end = previous_week_end" habe ich natürlich keine angefangene Woche mehr
Nun bleibt halt nur das Problem mit den Zeiträumen, bei denen dann Wochen am Anfang oder Ende nicht komplett sind.

Gruß
Christian

Code Auswählen


| 2020-09-13 19:05:00 | PV_Anlage_1_API | diff_week_Statistic_GridFeedInYear | 161360.0000 |
| 2020-09-20 18:05:00 | PV_Anlage_1_API | diff_week_Statistic_GridFeedInYear | 147630.0000 |
| 2020-09-27 17:05:00 | PV_Anlage_1_API | diff_week_Statistic_GridFeedInYear | 74480.0000  |
| 2020-09-30 19:05:00 | PV_Anlage_1_API | diff_week_Statistic_GridFeedInYear | 28230.0000  |    <<<<< erster Lauf mit timestamp_end = previous_month_end
| 2020-10-04 19:05:00 | PV_Anlage_1_API | diff_week_Statistic_GridFeedInYear | 47480.0000  |
| 2020-10-11 16:05:00 | PV_Anlage_1_API | diff_week_Statistic_GridFeedInYear | 8760.0000   |
| 2020-10-18 14:05:00 | PV_Anlage_1_API | diff_week_Statistic_GridFeedInYear | 450.0000    |
| 2020-10-22 17:57:00 | PV_Anlage_1_API | diff_week_Statistic_GridFeedInYear | 612.8400    |   <<<<< zweiter Lauf mit ohne timestamp_end
+---------------------+-----------------+------------------------------------+-------------+

Code Auswählen


defmod LogDBRep_Statistic_EnergyFeedInGrid_Month_diff_Week DbRep LogDB
attr LogDBRep_Statistic_EnergyFeedInGrid_Month_diff_Week DbLogExclude .*
attr LogDBRep_Statistic_EnergyFeedInGrid_Month_diff_Week aggregation week
attr LogDBRep_Statistic_EnergyFeedInGrid_Month_diff_Week allowDeletion 0
attr LogDBRep_Statistic_EnergyFeedInGrid_Month_diff_Week device PV_Anlage_1_API
attr LogDBRep_Statistic_EnergyFeedInGrid_Month_diff_Week diffAccept 15000
attr LogDBRep_Statistic_EnergyFeedInGrid_Month_diff_Week reading Statistic_GridFeedInYear
attr LogDBRep_Statistic_EnergyFeedInGrid_Month_diff_Week room Strom->Energie,System
attr LogDBRep_Statistic_EnergyFeedInGrid_Month_diff_Week timestamp_begin current_year_begin

DS_Starter · 22 Oktober 2020, 19:26:49

Hallo Christian,

ich mache gerade ein bisschen Urlaub und bin gerade von einer Wanderung zurück gekommen.
Mal sehen ob es mir gelingt ein paar sinnvolle Gedanken in FHEM zu investieren.

Zu deiner ersten Frage ...

Zitat
Hierbei geht es mir um einen Zähler, der den ganzen Tag ansteigt. Um das aufzuräumen können später alle Einträge bis auf den letzten gelöscht werden.
Die nächsten Schritte wären dann beim Monatszähler die jeweiligen Wochen zu behalten und
beim Jahreszähler die jeweiligen Monate.

Also wenn es sich um einen stetig steigenden Zähler handelt, kannst du einfach mit maxValue und dem Zusatz "deleteOther" arbeiten.
Konkret könnte es so aussehen, dass du die Zeitattribute auf current_Day_begin bzw. current_Day_end stellst und aggregation = day + device, reading. Danach:

Code Auswählen


set <> maxValue deleteOther

Übrig bleibt der höchte (=letzte) Wert des Tages.
Das gleiche kannst du machen mit current_week_begin und aggregation=week oder current_month_begin und aggregation = month usw.
Ich denke du verstehst wie ich das Konstrukt meine.
Dein SQL übernehme ich gerne wieder mit ins Wiki. Irgendwann werde ich mich mal wieder intensiver mit DbRep befassen und SQL-specials einbauen.

Über deine zweite FRage denke ich mal nach ....

ch.eick · 22 Oktober 2020, 19:41:42

Erst mal einen schönen Urlaub

Zitat von: DS_Starter am 22 Oktober 2020, 19:26:49
Also wenn es sich um einen stetig steigenden Zähler handelt, kannst du einfach mit maxValue und dem Zusatz "deleteOther" arbeiten.
Konkret könnte es so aussehen, dass du die Zeitattribute auf current_Day_begin bzw. current_Day_end stellst und aggregation = day + device, reading. Danach:

Code Auswählen Erweitern
set <> maxValue deleteOther
Übrig bleibt der höchte (=letzte) Wert des Tages.
Das gleiche kannst du machen mit current_week_begin und aggregation=week oder current_month_begin und aggregation = month usw.

Das erzeugt aber einen weiteren Eintrag mit einem generierten Namen

Zitat
Dein SQL übernehme ich gerne wieder mit ins Wiki. Irgendwann werde ich mich mal wieder intensiver mit DbRep befassen und SQL-specials einbauen.

Da solltest Du Dich bitte gegebenenfalls vorher melden, damit Du auch aktuelles hast. Ich denke die Zeit für das Wiki finde ich auch noch.

Zitat
Über deine zweite Frage denke ich mal nach ....

Immer wieder gerne...

DS_Starter · 22 Oktober 2020, 19:43:55

So jetzt die zweite Frage ...

Wenn ich alles richtig verstanden habe, würde ich es so lösen:

1. Zeigrenze timestamp_begin auf current_year_begin setzen (wenn das aufzuteile Jahr das aktuelle ist)
2. aggregation = week einstellen
3. maxValue laufen lassen (wenn MAX gesucht ist), ggf. auch hier wieder mit deleteOther wenn das gewünscht ist

Damit bekommst du für jede Kalenderwoche deinen Wert, auch wenn innerhalb der Woche ein Monatwechsel oder Jahreswechsel stattfindet.

LG,
Heiko

DS_Starter · 22 Oktober 2020, 19:49:08

Danke für deine Wünsche.

Zitat
Das erzeugt aber einen weiteren Eintrag mit einem generierten Namen

Denke nicht, das wäre bei writeToDB der Fall

Zitat
Da solltest Du Dich bitte gegebenenfalls vorher melden, damit Du auch aktuelles hast. Ich denke die Zeit für das Wiki finde ich auch noch.

Mache ich gerne, dauert auch noch etwas weil ich momentan ein neues Modul für die Integration der Synology File Station erstelle.
Freue mich auch darüber wenn du den Eintrag ins Wiki übernehmen würdest. Dann kann ich die Zeit für die Weiterarbeit am Modul nutzen.

ch.eick · 22 Oktober 2020, 20:08:44

Zitat von: DS_Starter am 22 Oktober 2020, 19:49:08
Denke nicht, das wäre bei writeToDB der Fall

Tja, da soll es ja hin

oder halt alle anderen löschen, das SQL ist fast fertig, muss aber noch getestet werden.

DS_Starter · 22 Oktober 2020, 20:25:26

ZitatTja, da soll es ja hin oder halt alle anderen löschen

Naja ich hatte es so verstanden dass du eigentlich nur den höchsten = letzten Wert einfach nur behalten wolltest und alle anderen im Bewertungszeitraum löschen. Egal, viele Wege führen nach Rom

Bin gespannt auf deine fertiges SQL ...

ch.eick · 22 Oktober 2020, 21:07:30

Zitat von: DS_Starter am 22 Oktober 2020, 20:25:26
Naja ich hatte es so verstanden dass du eigentlich nur den höchsten = letzten Wert einfach nur behalten wolltest und alle anderen im Bewertungszeitraum löschen. Egal, viele Wege führen nach Rom

Das ist schon richtig, wenn man jedoch wieder einen neuen reading Namen erzeugt, muss man den auch wieder in den Plot bringen.
Ich dachte eher daran, die Werte nur auszudünnen, dann sind die aktuellen im Plot noch dichter und werden beim Zurückblättern halt weniger granular.

Okay, mann kann auch alle readings im Plot definieren und die Kurven ergänzen sich dann grafisch. Das mache ich manchmal mit positiven Werten in Grün und negative Werte in rot.
Was mich halt etwas stört ist, dass man dann bei einem Select immer daran denken muss beide Readings zu definieren.

Viele Grüße in den Urlaub
Christian

DS_Starter · 22 Oktober 2020, 21:20:28

ZitatDas ist schon richtig, wenn man jedoch wieder einen neuen reading Namen erzeugt, muss man den auch wieder in den Plot bringen.

Aber in dem oben beschriebenen Verfahren wird doch kein neuer Readingnamen erzeugt. Es bleibt nur der letzte = höchste Wert in der Reihe erhalten und alle anderen gelöscht. Der original Readingname bleibt dabei erhalten.
Mag sein dass ich heute bisschen auf der Leitung stehe ...

LG,
Heiko

ch.eick · 22 Oktober 2020, 22:40:36

Zitat von: DS_Starter am 22 Oktober 2020, 21:20:28
Wenn ich writeToDB verwende wird also ein neues reading erzeugt.
Wenn ich nur den maxValue verwende und gleichzeitig deleteOther, wird dann alles bis auf den Max Wert gelöscht. Das hört sich dann nach meinem Wunsch an.

Hier fehlte noch die Bestätigung und es ist genau wie Heiko sagte.

Bei "deleteOther" wird einfach alles andere gelöscht und
bei "writeToDB" entsteht ein zusätzlicher Eintrag mit anderem reading Namen.
Den Namen kann man mit "readingNameMap" teilweise ändern.

In diesem Zeitraum wurde mit "maxValue", "week" und "deleteOther" gearbeitet.

Code Auswählen


select TIMESTAMP,DEVICE,READING,VALUE   from  history   where DEVICE = 'Thermostat_WO' AND TIMESTAMP>'2020-08-10' AND TIMESTAMP<'2020-09-13' order by TIMESTAMP limit 100;
+---------------------+---------------+-----------+-------+
| TIMESTAMP           | DEVICE        | READING   | VALUE |
+---------------------+---------------+-----------+-------+
| 2020-08-12 19:56:23 | Thermostat_WO | room-temp | 28.0  |
| 2020-08-21 22:18:01 | Thermostat_WO | room-temp | 26.5  |
| 2020-08-29 21:26:10 | Thermostat_WO | room-temp | 24.5  |
| 2020-09-05 18:19:50 | Thermostat_WO | room-temp | 24.0  |
| 2020-09-12 21:16:02 | Thermostat_WO | room-temp | 25.0  |
+---------------------+---------------+-----------+-------+

Gruß
Christian

DS_Starter · 22 Oktober 2020, 22:44:15

Zitat
Wenn ich writeToDB verwende wird also ein neues reading erzeugt.
Wenn ich nur den maxValue verwende und gleichzeitig deleteOther, wird dann alles bis auf den Max Wert gelöscht.

Genau

Bin auf dein Ergebnis gespannt. Bis morgen ...

LG

awex102 · 26 Oktober 2020, 17:36:27

Hallo,

dein Modul DBRep habe ich im Einsatz, um mir aus einem Außentemperaturfühler das Tagesmittel ausgeben zu lassen.

Jetzt bin ich auf das Thema Grünlandtemperatursumme gestoßen und möchte das Thema gerne als Anregung in die Roadmap geben.

Es werden die positiven Tagesmitteltemperaturen addiert, bis sie 200 erreichen. Negative Tagesmitteltemperaturen werden verworfen. Zusätzlich wird ein ein Faktor pro Monat erstellt:
Aus Wikipedia: Es werden ab Jahresbeginn alle positiven Tagesmittel erfasst. Im Januar wird mit dem Faktor 0,5 multipliziert, im Februar mit dem Faktor 0,75, und ab März geht dann der ,,volle" Tageswert (mal Faktor 1) in die Rechnung ein.

Die GTS gibt in der Agrarwirtschaft den Zeitpunkt an, ab dem Pflanzen zu wachsen beginnen.

Was ich mir sehr gut in Kombi mit deiner Tagesmitteltemperatur - Berechnung vorstellen könnte, ist die Berechnung des GTS pro Jahr.
Z.B. ein get, das den tagesaktuellen Wert der Grünlandtemperatursumme zurückgibt. Bei GTS >= 200 gibt das reading keine neuen Werte aus, bleibt also stehen und man kann den Stichtagtag ablesen bzw. darauf in fhem reagieren und notify oder doif nutzen um z.B. eine Notification ans Handy zu senden. Und im nächsten Jahr von vorne.

Die Berechnung der Tagesmitteltemperaturen muss nach den Vorgaben des DWD erfolgen.

Danke und Gruß!