Hallo liebe Leute,
ich scheitere leider an folgender Aufgabe:
Ich möchte die Einschlatdauer eines Gerätes für eine bestimmtes Intervall ermitteln.
Also "wie lange war der Lüfter in den letzten 6 Stunden eingeschaltet?"
Die Werte liegen in der Datenbank logdb vor.
Ich habe versucht etwas mit SQL-Abfrage zu ermitteln bin aber gescheitert (siehe https://forum.fhem.de/index.php/topic,79173.0.html (https://forum.fhem.de/index.php/topic,79173.0.html))
Kann jemand helfen
Ette
Hallo Ette,
gib mal bitte konkret an in welcher Form die Daten in der DB liegen, also Device / Reading / Value für einen Zeitraum.
Erstelle dir ein weiteres DbRep-Device, setzte die Attr device,reading auf die relevanten Eingrenzungen und setze auch timeDiffToNow auf 21600.
Dadurch werden immer die letzten 6 Stunden (21600 Sekunden) bei der Auswertung berücksichtigt.
Dann lass den Select laufen mit:
set <Name> fetchrows history
Nach der Ausführung den Browser aktualisieren und mal einen Screenshot der Ausgabe hier anhängen. Dann sehen wir vielleicht einen möglichen Weg die Daten auszuwerten.
Grüße
Heiko
Ich habe dann mal ein paar Werte künstlich mit enem at erzeugt.
1 bedeutet an und 0 aus (kleiner Scherz).
ich habe sichergestellt dass auf ein 0 immer en 1 im state fogt und umgekehrt.
So sieht meine Tabelle aus (hab sie ein bisschen mit Excell gradegebogen-bin zu doof die hier einzufügen):
2017-11-20_20-29-08__Ttim__state 1 20.11.17 20:37
2017-11-20_20-30-02__Ttim__state 0 20.11.17 20:37
2017-11-20_20-30-08__Ttim__state 1 20.11.17 20:37
2017-11-20_20-31-02__Ttim__state 0 20.11.17 20:37
2017-11-20_20-31-08__Ttim__state 1 20.11.17 20:37
2017-11-20_20-32-08__Ttim__state 0 20.11.17 20:37
2017-11-20_20-32-27__Ttim__state 1 20.11.17 20:37
2017-11-20_20-32-57__Ttim__state 0 20.11.17 20:37
2017-11-20_20-33-16__Ttim__state 1 20.11.17 20:37
2017-11-20_20-33-46__Ttim__state 0 20.11.17 20:37
2017-11-20_20-34-05__Ttim__state 1 20.11.17 20:37
2017-11-20_20-34-35__Ttim__state 0 20.11.17 20:37
2017-11-20_20-34-54__Ttim__state 1 20.11.17 20:37
2017-11-20_20-35-24__Ttim__state 0 20.11.17 20:37
2017-11-20_20-35-43__Ttim__state 1 20.11.17 20:37
2017-11-20_20-36-13__Ttim__state 0 20.11.17 20:37
2017-11-20_20-36-32__Ttim__state 1 20.11.17 20:37
2017-11-20_20-37-02__Ttim__state 0 20.11.17 20:37
2017-11-20_20-37-21__Ttim__state 1 20.11.17 20:37
Hoffe das hilft
Gruß
Ette
Warum löst du das nicht mit dem Modul Hourcounter in Fhem?
https://fhem.de/commandref.html#HourCounter (https://fhem.de/commandref.html#HourCounter)
Wenn ich einmal den Mechanismus verstanden habe, möchte ich mehrere Datenbankabfragen machen und in Abhängigkeit davon Aktoren schalten.
Wenn ich HourCounter richtig verstanden habe kann ich damit nicht alles machen was ich benötige.
Gruß
Ette
Hallo ette,
hourcounter wäre, wie maci schon schrieb, für diese Datenart die einfachste Variante.
Aber ich habe verstanden dass du Mehrwert aus den gespeicherten Daten der Datenbank ziehen willst.
Umfangreiche Auswertungen kannst du mit Hilfe der userExitFn (Attribut) anfertigen bzw. dir programmieren. Allerdings sind dann dazu Perl Programmierkenntnisse nötig.
Wie man damit z.B. eine komplexe Auswertung seiner PV-Anlage anfertigen kann ist hier beschrieben:
https://wiki.fhem.de/wiki/Datenbankgest%C3%BCtzte_Erstellung_der_Energiebilanz_einer_SMA_PV-Anlage_mit_%C3%9Cberschu%C3%9Feinspeisung
Für deine spezielle Aufgabenstellung würde ich jetzt mal probieren ein Beispiel zu erstellen um daran die Wirkungsweise zu erläutern und wenn es passt auch im Wiki abzulegen.
Wäre das ein Weg den du mitgehen würdest bzw. der dir helfen würde ?
Grüße
Heiko
Nun habe ich die Zeit gefunden um das Beispiel zu bauen.
Das Beispiel ermittelt aus der Datenbank die Einschaltdauer eines Kühlschrankes (über Zwischenstecker) innerhalb der letzten 24 Stunden.
Dabei wird angenommen, dass "Aus" = 0 ist und "Ein" ein von 0 abweichender Wert. (1 oder eine andere Zahl).
Damit wird es bei dir Ette ebenfalls funktionieren.
Es sind zwei Komponenten notwendig, ein DbRep-Device und eine Subroutine "switchontime" in der 99_myUtils. Ich habe die Routine in der hier angehängten 99_myOntimeUtils.pm eingebaut. Du kannst diese Datei runterladen, in dein FHEM-Verzeichnis kopieren (/opt/fhem/FHEM).
Die Daten in der Datenbank befinden sich im Device "eg.az.fridge_Pwr" und Reading "power".
Das DbRep-Device wird entsprechend dieser Raw-Definition angelegt:
define Rep.powerOnTime DbRep LogDB
attr Rep.powerOnTime aggregation no
attr Rep.powerOnTime comment Ermittlung der Anschaltdauer (Switch-on Time) eines Gerätes (z.B. Kühlschrank) \
innerhalb der letzten 24 Stunden.\
Es wird die Auswertungsroutine "switchontime" im Attribut "userExitFn" benötigt \
(in 99_myOntimeUtils.pm enthalten).\
\
Start erfolgt mit: "set <;Name>; fetchrows history"
attr Rep.powerOnTime devStateIcon connected:10px-kreis-gelb .*disconnect:10px-kreis-rot .*done:10px-kreis-gruen
attr Rep.powerOnTime device eg.az.fridge_Pwr
attr Rep.powerOnTime event-on-update-reading state,switch_on_time_sec
attr Rep.powerOnTime reading power
attr Rep.powerOnTime room DbLog
attr Rep.powerOnTime timeDiffToNow 86400
attr Rep.powerOnTime userExitFn switchontime
attr Rep.powerOnTime verbose 2
Natürlich ist das DbLog-Device (LogDB), sowie Attribute device, reading den eigenen Gegebenheiten anzupassen.
Wichtig ist die Angabe "attr Rep.powerOnTime userExitFn switchontime" was die Aktivierung der userExit-Funktion bzw. der Auswertungsroutine bewirkt.
Innerhalb der heruntergeladenen Datei 99_myOntimeUtils.pm ist der String in Zeile:
my $pattern = "eg.az.fridge_Pwr__power";
so abzuändern dass ein auswertbarer Teilstring des Readingsnamens mit den Switch-on/Switch-off Informationen enthalten ist.
In deinem Fall wäre das z.B.:
my $pattern = "Ttim__state";
Nun die Utils-Datei laden mit:
reload 99_myOntimeUtils.pm
oder FHEM Restart.
Die Auswertung wird nun gestartet mit:
set Rep.powerOnTime fetchrows history
Es werden zwei zusätzliche Readings im Device Rep.powerOnTime generiert, switch_on_time und switch_on_time_sec (siehe Screenshot).
switch_on_time_sec ist die summarische "EIN"-Zeit in Sekunden der letzten 24 Stunden und kann gut zur weiteren Auswertung genutzt werden.
switch_on_time ist eine in hhh:mm:ss formatierte Angabe zur leichteren Lesbarkeit.
Natürlich kann man switch_on_time_sec wiederum loggen um z.B. daraus eine grafische Anzeige zu erstellen, die die Entwicklung der täglichen "EIN"-Zeiten über einen längeren Zeitraum darstellen könnte (z.B. Heizzeiten).
Mit kleinen Abwandlungen könnten zum Beispiel auch die täglichen Raumlüftungszeiten (Fenter "open", "closed") ausgewertet und überwacht werden.
Wird die vorgegeben "open"-Zeit unterschritten, könnte ein Alarm ausgelöst werden.
In der Datei 99_myOntimeUtils.pm habe ich noch weitere Kommentare hinterlegt um die Arbeitsweise möglichst verständlich darzustellen.
Ich hoffe das Beispiel hilft die Möglichkeiten zu verstehen und deine Aufgabe zu lösen.
EDIT: Nun auch im Wiki abgelegt: https://wiki.fhem.de/wiki/Summe_aller_Einschaltzeiten_eines_Ger%C3%A4tes
Grüße
Heiko
Super,
muss jetzt aber zum Elternsprechtag - werde das am Wochenende mal ausprobieren.
Vielen Dank
Ette
Hallo DS_Starter,
erstmal vielen Dank für deine Arbeit.
Ich habe mir das Beispiel jetzt länger angeschaut, habe rumprobiert und dabei sehr viel gelernt.
Leider ist das Ergebnis immer noch fehlerhaft, da der Algorithmus die Readings wohl in umgekehrter Reihenfolge benötigt.
Lässt sich die Sortierung in DbRep irgendwie umkehren?
Sonst müsste ich den Algorithmus anpassen.
Liebe Grüße
Ette
Hallo Ette,
ja, es ist so dass die Datenzeilen DESC (also die neuesten zuerst) geliefert und ausgewertet werden.
Das hat damit zu tun dass die Rooutine auf dem normalen "set ... fetchrows history" beruht. Dabei sollen die neuesten x angezeigt werden fall x das fetch-Limit übersteigt.
Die Lieferreihenfolge lässt sich zur Zeit nicht umstellen, aber ich könnte ein Attribut vorsehen welches genau das Gegenteil einstellt.
Dann müsstest du den Code in der myUtils trotzdem etwas anpassen, weil sich die Timestamps der gelieferten Readings dann nicht mehr von "oben nach unten" sondern von "unten nach oben" ändern.
Aber währe das ein hilfreiches feature.
Was meinst du, soll ich dir mal eine Testversion zur Verfügung stellen ?
Grüße
Heiko
Hallo Heiko,
natürlich würde ich das testen, wenn ich ein Modul in FHEM damit ein Stück weiterbringen könnte.
attr Rep.powerOnTime order DESC
attr Rep.powerOnTime order ASC
???
Der Code muss eh angepasst werden und das traue ich mir trotz bestehdem Welpenschutz auch zu.
Liebe Grüße
Ette
Hallo Ette,
Zitat
attr Rep.powerOnTime order DESC
attr Rep.powerOnTime order ASC
Ja, fast getroffen. ;)
Es ist das Attribut "fetchRoute", d.h.:
attr Rep.powerOnTime fetchRoute descent
attr Rep.powerOnTime fetchRoute ascent
Dier ersion findest du hier:
https://forum.fhem.de/index.php/topic,53584.0.html
LG,
Heiko
Hallo zusammen,
ich habe mich nochmal der Thematik beschäftigt und dabei festgestellt, dass der Algorithmus in der Auswertungsroutine "switchontime" die Einschaltdauer vom Start bis zum ersten Ausschalten und die Zeit vom letzten Einschalten bis zum Ende nicht beachtet.
Um das noch einzubauen benötige ich natürlich den Start- und den ENdzeitpunkt des Gerätes.
Ich habe es mit $startzeit = AttrVal("Rep.powerOnTime","timestamp_begin",98); versucht, erhalte aber immer nur 98 als Ergebnis.
Kann mir da jemand den entscheidenden Tip geben?
Ich könnte das selber in aus "heute" und timeDiffToNow errechnen und in einem Dummy ablegen, das scheint mir aber weder elegant noch übersichtlich.
P.S.: an der Reihenfolge lag es nicht.
Herzlichen Gruß
Ette
Hallo Ette,
wenn du das Attr timeDiffToNow gesetzt hast existiert kein Attr timestamp_begin. Deswegen erhältst du mit "$startzeit = AttrVal("Rep.powerOnTime","timestamp_begin",98);" immer den default=98.
ZitatIch könnte das selber in aus "heute" und timeDiffToNow errechnen und in einem Dummy ...
Die Berechnung wäre schon richtig, aber warum in einem Dummy ablegen ?
Speichere dir doch diese berechneten Wert in einer Variable. Wäre mein Vorschlag.
LG,
Heiko
Hallo mal wieder,
ich habe jetzt die ganze Routine auf links gedreht und so funktioniert dieser feherfrei:
############################################################################
# $Id: myUtilsTemplate.pm 7570 2015-01-14 18:31:44Z rudolfkoenig $
# Beispiel aus https://wiki.fhem.de/wiki/Summe_aller_Einschaltzeiten_eines_Ger%C3%A4tes
package main;
use strict;
use warnings;
use POSIX;
use Time::Local;
sub
myOntimeUtils_Initialize($$)
{
my ($hash) = @_;
}
############################################################################################################
## Ermitteln der Einschaltzeiten eines Gerätes
############################################################################################################
# Im DbRep-Device ist das Attribut "userExitFn = switchontime" zu setzen.
# Auswertung wird im DbRep gestartet mit:
#
# set <DbRep-Device> fetchrows history
#
# Die resultierende Switch-on Zeit wird im aufrufenden DbRep-Device in Readings dargestellt:
# switch_on_time -> on-Zeit als formatierte Angabe hhh:mm:ss
# switch_on_time_sec -> on-Zeit als Sekundenwert
#
# Die Routine ist für alle Readings geeignet die Zahlenwerte als Switch-on/off Zustand beinhalten.
# Switch-off = 0
# Swich-off != 0
#
# Nach der Erstellung jedes einzelnen Readings wird die im Attribut "userExitFn" hinterlegte Funktion
# aufgerufen. Es wird der Devicename des aufrufenden Device, Reading und Wert des Readings übergeben.
# Der Aufrufablauf ist:
# 1. Start: state = running
# 2. Übermittlung jedes generierten Readings
# 3. Stop: state = done (bzw. nicht running)
#
###########################################################################################################
my $t = 0; # Variable für resultierende Sekunden
my $sekunden = 0; # Variable für sekunden des Readings
my $startzeit = 0; # Variable für Beginn des Zeitraums
my $endzeit = 0; # Variable für Ende des Zeitraums
my $letztervalue = 0; # Variable für Value des vorherigen Readings
my $letztersekunden = 0; # Variable für Sekunde des vorherigen Readings
my $initvalue = 0;
my $pattern = "Ttim__state"; # verwendbarer Teilstring des Switch-on/Switch-off Werte
# enthaltenden Readings (entsprechend anpassen !)
my $runde;
sub switchontime($$$)
{
my ($name,$reading,$value) = @_;
my $hash = $defs{$name};
# $value = int $value; #schöner für Anzeigen
if($reading eq "state" && $value eq "running") # der Select wurde gestartet
{
$letztervalue = -1;
$runde = 0;
my ($devn,$devread) = split("__",$pattern); # den aktuellen Zustand des Devices ermitteln
$devread = uc ($devread);
$initvalue = InternalVal("$devn","$devread",99);
if ($initvalue == 99)
{
Log3 $name, 1, "$name - der Patternname scheint falsch zu sein";
}
my $jetzt = TimeNow(); # aktuelle Zeit = Ende des Abfragezeitraums ermitteln
my $lstr = (split("__",$jetzt))[0];
my ($year, $month, $day, $hour, $min, $sec) = ($lstr =~ /(\d+)-(\d+)-(\d+) (\d+):(\d+):(\d+)/);
# Umwandeln in Epochesekunden
$endzeit = timelocal($sec, $min, $hour, $day, $month-1, $year-1900);
$startzeit = $endzeit - AttrVal("Rep.powerOnTime","timeDiffToNow",98); # Beginn des Abfragezeitraums ermitteln
Log3 $name, 3, "$name - UserExitFn \"ontime\" has been called.";
($sec,$min,$hour) = localtime($startzeit);
Log3 $name, 3, "Start der Abfrage $startzeit $hour:$min:$sec";
($sec,$min,$hour) = localtime($endzeit);
Log3 $name, 3, "Ende der Abfrage $endzeit $hour:$min:$sec";
$t = 0;
return;
}
$runde = $runde +1;
if($reading =~ /$pattern/) # den Timestamp unabhängig vom Status festhalten
{
my $lstr = (split("__",$reading))[0];
my ($year, $month, $day, $hour, $min, $sec) = ($lstr =~ /(\d+)-(\d+)-(\d+)_(\d+)-(\d+)-(\d+)/);
# Umwandeln in Epochesekunden
$sekunden = timelocal($sec, $min, $hour, $day, $month-1, $year-1900);
Log3 $name, 4, "t:$t s:$sekunden value:$value $hour:$min:$sec ";
if($letztervalue == -1) # wenn jüngster Wert (erstes reading)
{
if($value != 0) # an
{
$t = $endzeit;
}
}
if ($value == $letztervalue)
{
Log3 $name, 2, "$name - Ein value ist doppelt";
}
if( $value != 0 && $value != $letztervalue) # Gerät wurde eingeschaltet
{
$t = $t - $sekunden;
}
if($value == 0 && $value != $letztervalue) # Gerät wurde ausgeschaltet
{
$t = $t + $sekunden;
}
# Value sichern für endberechnung und doppelte Werte
$letztervalue = $value;
$letztersekunden = $sekunden;
}
if($reading eq "state" && $value ne "running") # die Selektion ist beendet, Summensekunden in Format hhh:mm:ss umwamdeln
{
if ($letztervalue == 0) # am Ende der Auswertung ist Status immer noch switch-on
{
$t = $t - $startzeit;
}
if ($letztervalue == -1) #wenn keine Werte kamen
{
if( $initvalue != 0 ) # Gerät ganze Zeit an
{
$t = AttrVal("Rep.powerOnTime","timeDiffToNow",98);
Log3 $name, 5, "Gerät ganze Zeit an";
}
if($initvalue == 0 ) # Gerät ganze Zeit aus
{
$t = 0;
Log3 $name, 5, "Gerät ganze Zeit an";
}
}
fhem("setreading $name switch_on_time_sec $t");
my $m = int $t/60;
my $s = $t - ($m*60);
my $h = int $m/60;
$m = $m - ($h*60);
my $timestring = sprintf("%03d:%02d:%02d",$h,$m,$s);
fhem("setreading $name switch_on_time $timestring (hhh:mm:ss)");
Log3 $name, 3, "$name - switch-on time was $timestring (hhh:mm:ss)";
}
return;
}
1;
Hab alles mehrfach getestet - viellicht kann man das ja so in die Wiki übernehmen.
Liebe Grüße und vielen Dank für die Hilfe
Ette
Guten Morgen,
prima !
Übernehme ich gerne so ins Wiki.
Vielen Dank und noch viel Spass beim Erkunden von DbRep :)
LG,
Heiko
Da ich das für meine Grafana Logs sowieso rumliegen habe, hier noch ergänzend die wie im ersten Post erfragte Lösung direkt mit einem SQL Statement.
Zum Nachvollziehen sich von innen nach außen über Punkte 1) bis 4) durcharbeiten.
Verwendete Zeitspanne im Beispiel ist "2018-01-29 00:00:00" bis "2018-01-29 10:00:00".
# -- 4) Nur Zeitspannen für den gewünschten Wert "1" selektieren und Zeitsumme bilden --
SELECT
SUM(on_time) as result
FROM (
# -- 3) Rückwärts durch alle gesammelten TimeStamp/Value Paare loopen und die Zeitdifferenz zum vorherigen Wertepaar ermitteln ( Entspricht Zeitspanne wie lang der Wert aktiv war) --
SELECT
time,
val,
# -- Hint: Fake-IF-Abfragen werden immer wahr. Wird verwendet um bei jedem Durchlauf die Differenz zum vorigen Timestamp zu ermitteln und danach die Variable @OLDTIME bei neu zu setzen --
IF (IF (@ONTIME := TIMESTAMPDIFF(SECOND, time, @OLDTIME), 1, 1) AND IF (@OLDTIME := time, 1, 1), @ONTIME , 0) as on_time
FROM (
(
# -- 1) Selektieren aller TimeStamp/Value Paare im gewünschten Zeitraum --
SELECT
TIMESTAMP as time,
VALUE as val
FROM history
# -- Hint: Variable @OLDTIME wird hier mit dem Endzeitpunkt initial belegt, damit die Berechnung in 3) als erstes Ergebnis die Zeitspanne Endzeit bis letzter Timestamp liefert --
JOIN (SELECT @OLDTIME := "2018-01-29 10:00:00") AS var
WHERE READING="MyFhemReading" AND DEVICE="MyFhemDevice" AND TIMESTAMP >= "2018-01-29 00:00:00" AND TIMESTAMP <= "2018-01-29 10:00:00"
)
UNION
(
# -- 2) Letzten Wert vor dem Startpunkt des Zeitraums ermitteln und diesen zum Startzeitpunkt zu den Werten aus 1) hinzufügen --
SELECT
"2018-01-29 00:00:00" as time,
VALUE as val
FROM history
WHERE READING="MyFhemReading" AND DEVICE="MyFhemDevice" AND TIMESTAMP <= "2018-01-29 00:00:00"
ORDER BY TIMESTAMP DESC
LIMIT 1
)
) a
ORDER BY time DESC
) b
WHERE val = "closed"
Es wird hier zuerst ein Select gemacht um alle Timestamps + Werte des Geräts im gewünschten Zeitraum abzuholen.
Danach läuft man rückwärts durch die Zeit von Endzeitpunkt bis Startzeitpunkt und berechnet zu jedem Zeitstempel die Dauer wie lang das Signal aktiv war.
Am Ende werden dann nur die Zeiten für den Wert rausgefiltert für den man sich interessiert (also "open" oder "closed") und eine Summe der Zeiten gebildet.
Ein Beispiel wenn man die verschachtelten Selects einzeln aufruft und sich die Rückgaben anschaut:
Rückgabe von Select 1:
time val
2018-01-29T07:22:26Z closed
2018-01-29T07:23:26Z open
2018-01-29T08:24:26Z closed
2018-01-29T08:25:26Z open
2018-01-29T09:43:26Z closed
2018-01-29T09:46:26Z open
Rückgabe von Select 3:
time val on_time
2018-01-29 00:00:00 open 26546
2018-01-29 07:22:26 closed 60
2018-01-29 07:23:26 open 3660
2018-01-29 08:24:26 closed 60
2018-01-29 08:25:26 open 4680
2018-01-29 09:43:26 closed 180
2018-01-29 09:46:26 open 814
Rückgabe von Select 4 (Summe der "closed" Werte):
result
300.00
Über Sinn und Unsinn kann man gerne streiten wenn man das Ergebnis sowieso wieder in FHEM weiterverwenden will.
Evtl. ist hier eine Perl Lösung einfacher wiederzuverwenden oder zu parametrieren.
Aber es geht eben auch so wenn man unbedingt will. ;)