UPDATE 31.10.: Amazon Alexa - ioBroker Skill läuft aus ?

[legendary83]

Guten Abend!

Ich bin gerade am verzweifeln und könnte kompetente Hilfe brauchen.

Es geht um Datenpunkte die Boolsche Werte enthalten und in history.0 sekündlich 10 sekündlich für ein Jahr geloggt wurden.
Nun muss ich auswerten wie lange z.B. für Monat Oktober 2024 der Datenpunkt den Wert True geführt hat und wie lange er den Wert False geführt hat.

Es geht genau genommen um Betriebsstunden einer Maschine.

Wie kann ich das mit einem Adapter oder mit einem Javascript bewerkstelligen?

So große Datenmengen lassen sich ja nicht einfach so exportieren.

Vielen lieben Dank für helfende Ratschläge!

[David G.]

@legendary83

Du kannst die Daten aus dem History Adapter über einen bestimmten Zeitraum anfragen. Dort erhält du einen Json mit den Werten.

Diese kannst du in einer Schleife durchlaufen und zahlen wie oft true oder false vorkommt.

EDIT

javascript.0	19:28:30.790	warn	script.js.Eigene_Scripte.Tests.forumhilfe: [ { ts: 1741149247548, val: false }, { ts: 1741149753270, val: true }, { ts: 1741150817089, val: true }, { ts: 1741152451067, val: false }, { ts: 1741179214212, val: false }, { ts: 1741179533337, val: true }, { ts: 1741179675992, val: true }, { ts: 1741182347831, val: false } ]
javascript.0	19:28:30.791	warn	script.js.Eigene_Scripte.Tests.forumhilfe: 4

[OliverIO]

@legendary83

ich habe die frage mal chatgpt gestellt.
wenn du die daten nach mysql/mariadb überführst, dann lässt sich das berechnen.

ich kopiere hier mal die anworten rein. beachte das sql wurde in der 2. antwort nochmal verfeinert, da es dort explizit dann um mariadb/mysql geht.
beachte auch, das das sql noch nicht an die konkrete tabellenstruktur des iobrokers angepasst worden ist, da die zuordnung datenpunkt zu id und id zu wert in 2 verschiedenen tabellen statt findet.

du kannst auch selbst in der kostenlosen version chatgpt verwenden. erkläre deine randbedingungen so genau wie möglich, dann kommen auch bei sql wirklich hervorragende ergebnisse raus. du kannst chatgpt auch anweisen dir fragen zu stellen, falls du dich schwer tust die rahmenbedingungen genau genug zu formulieren

für solche art abfragen sind datenbanken geradezu prädestiniert.
eine code lösung würde auch gehen, dauert aber erheblich länger.

1 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Angenommen, du hast eine Tabelle (beispielsweise mytable) mit mindestens den Spalten:

• zeitstempel (Datetime- oder Timestamp-Typ)
• status (BOOLEAN oder etwas Vergleichbares, das TRUE/FALSE angibt)

und in dieser Tabelle wird jede Statusänderung bzw. jeder Status zu einem bestimmten Zeitpunkt protokolliert. Dann lässt sich die „Gesamtzeit“, in der status = TRUE war, mit Hilfe von Window-Funktionen relativ elegant bestimmen.

Grundidee:
Wir sortieren die Einträge nach dem Zeitstempel und schauen uns immer das vorherige (LAG) TRUE/FALSE sowie den vorherigen Zeitstempel an. Wenn der vorherige Status TRUE war, addieren wir die Differenz zwischen dem aktuellen und dem vorherigen Zeitstempel.

Ein mögliches generisches SQL-Beispiel (etwa für PostgreSQL) könnte so aussehen:

WITH status_history AS (
    SELECT
        zeitstempel,
        status,
        LAG(status) OVER (ORDER BY zeitstempel)     AS prev_status,
        LAG(zeitstempel) OVER (ORDER BY zeitstempel) AS prev_zeitstempel
    FROM mytable
)
SELECT
    -- Beispiel: Gesamtzeit in Sekunden, wenn prev_status TRUE war
    SUM(
        CASE WHEN prev_status = TRUE
             THEN EXTRACT(EPOCH FROM (zeitstempel - prev_zeitstempel))
             ELSE 0
        END
    ) AS gesamtdauer_true_in_sekunden
FROM status_history;

Erklärung:

1. Im CTE (WITH-Teil) erzeugen wir eine status_history, in der wir mit den Window-Funktionen

   • LAG(status) OVER (ORDER BY zeitstempel) den Status der vorherigen Zeile,
   • LAG(zeitstempel) OVER (ORDER BY zeitstempel) den Zeitstempel der vorherigen Zeile

   bestimmen.

2. In der äußeren Abfrage summieren wir dann nur die Zeitdifferenz zwischen prev_zeitstempel und zeitstempel, wenn der vorherige Status = TRUE war.

   • EXTRACT(EPOCH FROM (zeitstempel - prev_zeitstempel)) liefert in PostgreSQL die Differenz in Sekunden.
   • Wenn du die Dauer in Minuten oder Stunden benötigst, könntest du anstelle von EPOCH auch andere Einheiten ausrechnen oder direkt die Differenz in Sekunden anschließend konvertieren.

3. Diese Summe ergibt die komplette Zeit, in der der Status „TRUE“ gewesen ist, unter der Annahme, dass bei einem Wechsel von FALSE auf TRUE auch wirklich ein Datensatz mit status=TRUE und passendem Zeitstempel erfasst wird.

Wichtige Hinweise / mögliche Erweiterungen

• Erster Eintrag: Wenn die erste Zeile in der Tabelle schon status=TRUE hat und damit vor dem ersten Eintrag möglicherweise schon eine Weile TRUE war, muss man klarstellen, ob und wie das berücksichtigt werden soll (z.B. durch einen „Beginn-Zeitpunkt“).

• Letzter Eintrag: Wenn der letzte Status TRUE ist und in der realen Welt eventuell immer noch TRUE bleibt, dann endet deine Berechnung an diesem letzten Zeitstempel. Je nach Use-Case musst du definieren, bis wann du rechnen möchtest (z.B. bis jetzt()).

• MySQL vs. PostgreSQL: In MySQL ist die Syntax ähnlich, allerdings benötigst du zur Differenz ggf. die Funktion TIMESTAMPDIFF(SECOND, prev_zeitstempel, zeitstempel) anstatt EXTRACT(EPOCH FROM ...). Achte auch darauf, dass MySQL erst ab Version 8.0 Window-Funktionen wie LAG() oder LEAD() unterstützt.

• Kein LAG verfügbar: Falls du eine ältere MySQL-Version hast (ohne Window-Funktionen), kann man das Ganze nur mühsamer über Joins lösen, indem man sich eine „Nachbarzeile“ via selbst-Join sucht. Es empfiehlt sich dann eher, zeitnah auf eine neuere Version oder ein System mit Window-Funktion-Unterstützung umzusteigen, da das Query damit deutlich einfacher und schneller wird.

In jedem Fall ist die Grundlogik:

1. Sortiere nach Zeitstempel.
2. Ermittele, in welchen Zeitintervallen dein Status TRUE war.
3. Summiere die Längen dieser Zeitintervalle.

Damit lässt sich die „Gesamt-TRUE-Zeit“ in Sekunden oder jeder anderen Zeiteinheit sehr gut ermitteln.

2.xxxxxxxxxxxxxxxxxxxxxx

Grundsätzlich ja – allerdings gibt es ein paar Voraussetzungen und kleinere Anpassungen, die du in MySQL/MariaDB beachten musst:

1. Window-Funktionen

   • LAG() (und andere Window-Funktionen) sind erst in MySQL 8.0 bzw. MariaDB 10.2 und höher verfügbar.
   • Falls du eine ältere Version verwendest, funktioniert das Statement nicht ohne Weiteres. Dann müsstest du (umständlicher) mit Joins oder einer anderen Logik arbeiten.

2. Common Table Expressions (CTE) mit WITH

   • CTEs (WITH status_history AS (...)) werden ab MySQL 8.0 und MariaDB 10.2 unterstützt. In älteren Versionen kannst du das so nicht verwenden und müsstest eine „Subselect“-Variante nutzen.

3. Zeitdifferenz in MySQL/MariaDB

   • Anders als in PostgreSQL gibt es kein EXTRACT(EPOCH FROM (…)). Stattdessen kannst du die Differenz zum Beispiel mit

     TIMESTAMPDIFF(SECOND, prev_zeitstempel, zeitstempel)
     

     oder mit

     UNIX_TIMESTAMP(zeitstempel) - UNIX_TIMESTAMP(prev_zeitstempel)
     

     ermitteln.

Anpassung des Beispiels für MySQL 8 / MariaDB 10.2+

Wenn du MySQL 8+ oder MariaDB 10.2+ hast, kann dein Query zum Beispiel so aussehen:

WITH status_history AS (
    SELECT
        zeitstempel,
        status,
        LAG(status) OVER (ORDER BY zeitstempel)        AS prev_status,
        LAG(zeitstempel) OVER (ORDER BY zeitstempel)   AS prev_zeitstempel
    FROM mytable
)
SELECT
    SUM(
        CASE
            WHEN prev_status = TRUE THEN TIMESTAMPDIFF(SECOND, prev_zeitstempel, zeitstempel)
            ELSE 0
        END
    ) AS gesamtdauer_true_in_sekunden
FROM status_history;

• prev_status = TRUE: Achte darauf, dass du wirklich einen Boolean (oder TINYINT) in der Spalte hast. Falls du TINYINT(1) und TRUE als 1 speicherst, kannst du auch WHEN prev_status = 1 THEN ... schreiben.

• TIMESTAMPDIFF(SECOND, ...) gibt dir die Differenz in Sekunden.

Ältere MySQL-/MariaDB-Versionen ohne Window-Funktionen

Wenn du keine Window-Funktionen (LAG) und keine CTEs hast, wird es etwas aufwendiger:

• Du müsstest z.B. jede Zeile mit der folgenden Zeile (Self-Join) verknüpfen, um herauszufinden, wann ein TRUE-Zustand beginnt und wann er endet.
• Anschließend kannst du die Differenzen der Zeitstempel summieren, wo der Status TRUE war.

Die kürzeste und verständlichste Lösung ist jedoch immer mit CTE und Window-Funktionen, sofern deine Datenbankversion das unterstützt.

Fazit:

• Mit MySQL 8.0+ oder MariaDB 10.2+ ist das vorgestellte Statement (ggf. mit TIMESTAMPDIFF) lauffähig.
• Für ältere Versionen bräuchtest du eine alternative Lösung ohne CTE und Window-Funktionen.

[David G.]

@oliverio

Klappt das aus dem History Adapter mit SQL befehlen?

[OliverIO]

@oliverio sagte in Adapter history.0 auswerten für 16 Datenpunkte und 12 Monate:

wenn du die daten nach mysql/mariadb überführst, dann lässt sich das berechnen.

[David G.]

@oliverio

Dann ja, aber ob der Aufwand lohnt.....
Bei meiner Variante kommt natürlich eine ordentliche Liste raus. Jede Sekunde für ein Jahr.....

Da könnte man ggf die Schrittgröße was anpassen.
Im Schnitt Sollte das Ergebnis ja trotzdem Stimmen, wenn die Maschine nicht immer nur wenige Sekunden an war.

[OliverIO]

@david-g

er hat ergebnisoffen gefragt.
es geht um 16 datenpunkte
ja die migration und die installation weiterer systeme (adapter / datenbank) bedeutet aufwand
das schreiben und testen von code ebenfalls
wenn er das wiederholt messen und ausgeben möchte, dann würde ich definitiv für datenbank plädieren.

die migration ist hier beschrieben und entsprechende skripte stehen bereit
https://github.com/ioBroker/ioBroker.history/blob/master/docs/de/README.md#history-daten-in-sql-oder-influxdb-übernehmen

ich fürchte eine einfache blockly lösung wird es hier nicht geben, entweder javascript oder datenbank. für datenbank ist die lösung zu 80/90% fertig, nur noch feinjustierung. hast du entsprechende codebeispiele in javascript gefunden? Das zählen von true oder false wird nicht reichen. er will ja die betriebszeit berechnen. Also die vergangene Zeit, zwischen dem jeweiligen Statuswechsel

[David G.]

@oliverio sagte in Adapter history.0 auswerten für 16 Datenpunkte und 12 Monate:

hast du entsprechende codebeispiele in javascript gefunden?

In der Theorie klappt mein Blockly vom oben perfekt.
Die Frage ist nur, ob die Abfrage nicht zu groß ist (deshalb evtl die Schritte von 1sek auf 1min stellen).

EDIT
P. S.
Wenn der TE so viel loggt, ist der history vermutlich eh nicht das allerbeste.

[OliverIO]

@david-g sagte in Adapter history.0 auswerten für 16 Datenpunkte und 12 Monate:

In der Theorie klappt mein Blockly vom oben perfekt

aber es zählt doch nur.
wieviel zeit war jetzt an/true
also in Sekunden/Minuten/Stunden

oder habe ich die Anfrage falsch verstanden?

[David G.]

@oliverio

Er hat ja jede Sekunde geloggt ob an oder aus.
Also so verstehe ich das.
60 Werte in einer Minute.

Ansonsten kann man in der Schleife ja jeden Ts von false mit dem vorherigen TS verrechnen.
Zähle i von 1 bis lange von Liste.
Falls false rechne ts von i abzüglich ts von i-1.
Die Ergebnisse in der Schleife addieren.

[OliverIO]

@david-g

Ah ja da steht sekündlich. Auch wenn das nicht immer exakt ist kann es bei vielen datenpunkten zu Ungenauigkeiten kommen kann.
Aber das hängt natürlich Davon ab, ob ihm das schon reicht

[David G.]

@oliverio sagte in Adapter history.0 auswerten für 16 Datenpunkte und 12 Monate:

Auch wenn das nicht immer exakt ist kann es bei vielen datenpunkten zu Ungenauigkeiten kommen kann.

Aber da sprechen wir ja vermutlich um ms. Es kommt ja nur auf den Wechsel von true/false an. Das wird ja vermutlich nicht 1000x am Tag passieren.
Und ich denke der geloggte TS ist schon exakt oder? Wird ggf nur was verspätet geschrieben.

[OliverIO]

@david-g

Bevor man hierzu viele Annahmen macht, muss der TE Sich äußern, was ihm lieber ist Und was die genauen Bedingungen sind

[David G.]

@oliverio

Das stimmt.
Hab grad aber mal aus Interesse getestet wie es klappt, falls nur Änderungen aufgezeichnet werden.

Könnte so in der Art klappen. Hab das Ergebnis aber nicht verifiziert.

Edit

Geht so aber nur, wenn "nur Änderungen aufzeichnen" oder wie das heißt aktiv ist.
Falls nicht, müsste man das noch abfangen.

[paul53]

@david-g sagte: Könnte so in der Art klappen.

Muss nicht das Attribut result vom Parameter result ausgewertet werden (siehe Doku)?

[David G.]

@paul53 sagte in Adapter history.0 auswerten für 16 Datenpunkte und 12 Monate:

@david-g sagte: Könnte so in der Art klappen.

Muss nicht das Attribut result vom Parameter result ausgewertet werden (siehe Doku)?

In meinen Debugbausteinen waren zumindest immer schön alle Werte drinnen.

Versuche mich grad an der Berechnung, wenn true und false wiederholt geschrieben werden (Habe ich nämlich bei dem Fenster so, was ich oben zum test genommen habe, das Ergebnis ist zu kurz) . Da scheitere ich grad noch ^^.
Da muss man ja den ersten TS von true und den letzten TS von false verrechnen. Da ist es glaube zu spät für....

[paul53]

@david-g sagte: waren zumindest immer schön alle Werte drinnen.

Ach ja: Blockly verwendet anstelle von sendTo() die Funktion getHistory().

[paul53]

@legendary83 sagte: Betriebsstunden einer Maschine.

Per Blockly kann man es so versuchen:

EDIT: obj für erstes Element korrigiert.

[David G.]

@paul53

Auch wenn es gtad leicht offtopic wird.
Hast du spontan eine Idee für mein eben geschriebenes Problem?
Die Logik von meinem Beispiel klappt ja nicht, wenn die Werte doppelt geschrieben werden.

EDIT
Da warst du schneller als ich fragen konnte ^^.
Hast du einen Export für mich?

[paul53]

@david-g sagt: Berechnung, wenn true und false wiederholt geschrieben werden

Schau auf lastValue in meinem Vorschlag.