Dauer aus Datenbank berechnen

[DS_Starter]

Guten Morgen,

prima !
Übernehme ich gerne so ins Wiki.

Vielen Dank und noch viel Spass beim Erkunden von DbRep 

LG,
Heiko

[Thyraz]

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".

Code Auswählen Erweitern


# -- 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:

Code Auswählen Erweitern


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:

Code Auswählen Erweitern


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):

Code Auswählen Erweitern


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.