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:
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):
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![Smile :) :)](/styles/default/xenforo/smilies/smile.png)
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:
SQL:
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):
PHP:
$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
![Smile :) :)](/styles/default/xenforo/smilies/smile.png)