Ladevorgänge (eAuto) mit einem SQL-Query anhand von Stromzählerdaten erkennen

[dbfrank]

Hallo zusammen,

ich bin neu hier, nicht sonderlich geübt in komplexeren SQL-Abfragen und bereits mit einer ziemlich einfachen Aufgaben ein wenig überfordert.

Kurz als Einleitung:
Ich habe ein Elektrofahrzeug und lade dieses sehr häufig zuhause. Die einzelnen Ladevorgänge (Start, Ende, Kilometerstand, geladene kWh) muss ich dokumentieren.
Aus diesem Grund hängt der Stromkreis mit dem Fahrzeug an einem eigenen (geeichten) Stromzähler mit S0 Impulsausgang. Pro kWh werden 2000 Impulse mit einer Impulslänge von 30ms gegeben.
Jene Impulse werte ich mit einem Raspberry-Pi aus und sende sie (über einen GET-Request) an ein Script, welches sie in eine PostgreSQL (mit TimescaleDB Extension) schreibt.
In Grafana kann ich dann auch sehr gut den jeweiligen Stromverbrauch visualisieren.

In die Datenbank werden geschrieben: Start des impulses, Ende des impulses, Anzahl der bisherigen Impulse (laufender Counter).
Der laufende Counter geteilt durch 2000 entspricht auch der Anzeige des Stromzählers.
Bei einem Neustart (z.B. wegen Stromausfall) des Raspberries werden wird zwar sofort begonnen die Werte zu erfassen, aber erst an die Datenbank geschrieben nachdem ich den aktuellen Zählerwert eingegeben und eine Taste gedrückt habe. Damit wird der durchlaufende Counter wieder mit den gezählten Impulsen synchronisiert.

Aber nun zu meinem eigentlichen Problem:
der Stromverbrauch zum Zeitpunkt X kann man bestimmen, indem man sowohl die Counter, als auch die Timestamps der Datenbankeinträge voneinander subtrahiert und die Counterdifferenz durch die Zeitdifferenz teilt.
Beispiel:
1.) 2023-05-14 06:32:37.05+02 - 194715
2.) 2023-05-14 06:33:37.91+02 - 194716
Im Zeitraum von 60,86 Sekunden hat sich der Counter um den Wert 1 erhöht.
Dies entspricht 59,15 Counter pro Stunde = 29,58 Watt Verbrauch.
Das entspricht dem Grundverbrauch von Wallbox, Raspberry, Netzteil, Beleuchtung, etc.

So sieht es z.B. während einem Ladevorgang aus:
1.) 2023-05-26 18:12:26.53+02 - 622968
2.) 2023-05-26 18:12:26.70+02 - 622969
...
2000.) 2023-05-26 18:18:07.60+02 - 624.968

Der durchschnittliche Leistung zwischen Zeile 2000 und Zeile 1 liegt bei
2000 Countern in 341,49 Sekunden = 1kWh pro 341,49 Sekunden = 10,54 kW Verbrauch

Damit die jeweilige Leistung zum Zeitpunkt X für mich besser ersichtlich ist, habe ich mir eine View gebaut:

SELECT
    zaehlerwerte.counterstart,
    zaehlerwerte.counter,
    lag(zaehlerwerte.counter) OVER (ORDER BY zaehlerwerte.counterstart) AS lastcounter,
    lag(zaehlerwerte.counterend) OVER (ORDER BY zaehlerwerte.counterstart) AS lastcounterend,

    (zaehlerwerte.counter - lag(zaehlerwerte.counter) OVER (ORDER BY zaehlerwerte.counterstart))::double precision / date_part('epoch'::text, zaehlerwerte.counterstart - lag(zaehlerwerte.counterend) OVER (ORDER BY zaehlerwerte.counterstart)) * (60 * 60 / 2)::double precision AS watt
   FROM zaehler.zaehlerwerte

Jetzt sehe ich zu jedem Zeitpunkt den Stromverbrauch in Watt.


Nun möchte ich daraus eine Liste der Ladevorgänge erzeigen.
Also Start der Ladung, Ende der Ladung, geladene kWh.
(den Kilometerstand hole ich mir aus einer anderen Tabelle, welche sich per Fernzugriff auf das Fahrzeug regelmäßig die Kilometerstände abruft).

Als Definition für einen aktiven Ladevorgang habe ich einen Stromverbrauch von über 500 Watt angenommen.
Da ich es mit SQL nicht hinbekommen habe, wollte ich es mit einem PHP-Script machen.
Hierbei habe ich erstmal alle Werte über 500 Watt aus der Datenbank (kellerzaehler-verbrauch ist die View) in ein Array geschrieben, und habe für jeden Eintrag in diesem Array ein weiteres Query an die Datenbank gestellt, welches mir den Endzeitpunkt der Ladung (sobald der Wert unter 500 Watt fällt heraussucht):

$result = pg_query($conn, "SELECT counter,watt FROM zaehler.kellerzaehler_verbrauch WHERE watt > 500 ");
$triggers = array();
while ($row = pg_fetch_array($result, null, PGSQL_ASSOC)) {
  $triggers[$row['counter']] = $row['watt'];
}
foreach ($triggers as $counter => $watt) {
        if ($counter < $max)
                continue;

        $watt = round($watt, 0);
        $result_min = pg_query($conn, "SELECT counterstart,counter,watt FROM zaehler.kellerzaehler_verbrauch WHERE watt < 100 AND COUNTER < $counter ORDER BY counter DESC LIMIT 1");
        $row_min = pg_fetch_array($result_min, null, PGSQL_ASSOC);
        $min = $row_min['counter'];
        $counterstart = $row_min['counterstart'];

        $result_max = pg_query($conn, "SELECT lastcounterend,counter,watt FROM zaehler.kellerzaehler_verbrauch WHERE watt < 100 AND COUNTER > $counter ORDER BY counter ASC LIMIT 1");
        $row_max = pg_fetch_array($result_max, null, PGSQL_ASSOC);
        $max = $row_max['counter'];
        $lastcounterend = $row_max['lastcounterend'];
        $geladen = $max - $min;
        echo "$counterstart;$min;$lastcounterend;$max;$geladen\n";
}

Das Problem:
Manchmal bricht während des Ladevorgangs die Leistung kurz ein... also unter 500 Watt... manchmal auch unter 100 Watt... Meistens nur ein Wert. Maximal 2.
Bei jedem dieser Einbrüche wertet das Script nun leider die Ladung als abgeschlossen und beginnt eine neue Ladung zu erfassen.

Deshalb möchte ich es gerne etwas intelligenter gestalten.
Eine Ladung beginnt, wenn die Last über 30 Sekunden dauerhaft über 500 Watt steigt.
Eine Ladung endet, wenn die Last über 30 Sekunden dauerhaft unter 250 Watt fällt.

Habt ihr eine Idee, wie ich das am besten umsetzen kann?
Bzw. wäre es sogar direkt mit einem Query - ohne PHP-Script - möglich?

Weshalb ich PHP wählte war, dass ich zwar alle Werte über 500 Watt aus der Datenbank erfasse, aber über den Code "if ($counter < $max) continue;" alle Werte ignoriere, die vor dem (über das weitere Query ermittelte) Ende der Ladung liegen.
Ich muss also nicht für tausende Werte dazwischen erneut tausende Subqueries stellen.

Für jegliche Hilfen oder Denkanreize wäre ich sehr dankbar

 

[Kampfgummibaerlie]

Viel Text für die eigentliche Absicht...

Beispiel:

with recursive ladung(x) as (select '0 seconds'::interval, 500 as watt union select x+'1 seconds'::interval, 200 
from ladung where x <= '30 seconds'::interval)
select case when x >= '30 seconds'::interval then true else false end, to_char(now()::timestamp, 'DD.MM.YYYY HH24:MI')
from ladung where x <= '30 seconds'::interval
union
select case when x < '30 seconds'::interval then false else true end, to_char(now()::timestamp, 'DD.MM.YYYY HH24:MI')
from ladung where x <= '30 seconds'::interval

selbstverständlich mit den gegebenen Datenbankwerten teils ersetzen, ich denke aber rekursive abfrage ist dein Stichwort

ich habe mir das Beispiel aus den Haaren gezogen, wie ich deine Aufgabe verstanden habe.

 

[dbfrank]

Vielen Dank für den Gedankenanstoß.
Mit rekursiven Abfragen in SQL habe ich mich tatsächlich bisher noch nicht beschäftigt.
Habe den Syntax grob überflogen, aber benötige noch etwas mehr Zeit, um es komplett zu verstehen.

Bei deinem Beispiel werde ich wohl noch etwas länger brauchen, bis ich verstanden habe was es macht.

Eines ist mir aber noch unklar...
Soweit ich es verstanden habe, gibt es bei den rekursiven Abfragen eine "äußere" Query (den "non-recursive term"), und zu jedem Result der äußeren Query eine innere Query (den "recursive term")...

Nur sieht es bei einem Ladevorgang ja so aus, dass während dem Laden jede Menge Datenpunkte (teilweise über Stunden im Sekundentakt) mit jeweils über 500 Watt erzeugt werden. Die Rekursive query würde dann doch für jeden dieser tausende Datenpunkte erneut durchlaufen...

Die Datenbank mit den Werten aus dem letzten Jahr hat bereits über 2 Mio Einträge. Und in Zukunft werden es vermutlich deutlich mehr werden.
Wie sorgt die Query dafür, dass aus diesen 2 Mio Einträgen tatsächlich nur noch die 20-30 Ladevorgänge herauskommen, die ich letztes Jahr hatte?

 

[dabadepdu]

Es müssen nicht unbedingt recursive Abfragen sein.
Es müsste reichen, einen Mittelwert über eine Zeiteinheit zu bilden und wenn der den Schwellwert übersteigt oder unterschreitet (mglw. der Startschwellwert etwas niederiger als der für das Ende), dann hast Du Anfang und Ende.