Sensorwerte in SQL-DB schreiben

[IOMAX 0]

Hallo zusammen,

ich habe eine Frage für die ich über die Suche keine Antwort finden konnte.

Ausgangslage:
Ich habe 2 Powerfox Poweropti über den Adapter "powerfox2" an iobroker angebunden.
In den Objekten finden sich also zwei Devices (0 und 1) mit jeweils 10 Datenpunkten,
von denen mich insgesamt aber nur 9 interessieren.

Wenn ich die Werte nun über eine Instanz an eine SQL-DB schicke, dann habe ich zwar alles in einer (Maria)DB,
kann aber nicht zuordnen, welche Zeile zu welchem Datenpunkt von den 9 gehört.

Wenn ich als Workarround 9 SQL-DBs anlege, dann habe ich zwar alles separat, sodass ich erkennen kann,
welcher Datenpunkt wann welchen Wert hatte, die Visualisierung in Grafana klappt dann aber nicht, weil ich
teilweise mehrere Datenpunkte in einem Chart benötige. Grafana lässt leider immer nur eine Datenquelle pro Chart zu.

Ziel:
Wie kriege ich es mit iobroker hin, dass alle 9 Datenpunkte in einer Datenbank, separiert in 9 Tabellen,
gespeichert werden ?

Bestimmt mit Blocky, hab ich aber keine Möglichkeit gefunden.

Für Eure Unterstützung besten Dank im Voraus !

Maxi

[mickym]

@iomax-0 Nein das ist eigentlich ganz einfach. In Deinem SQL Statement selektierst Du mit _id, welcher Datensatz zu welchem Datenpunkt gehört.

Welche Nummer zu welchem Datenpunkt gehört - kannst Du aus der datapoints Tabelle ermiteln:

Du bist ja über SQL Statements ja jederzeit eigene Views zu erstellen, falls Du es so nicht selektieren willst.

Falls Grafana weder mit SQl Statements noch mit Views umgehen kann, dann ist das eher ein Problem von Grafana - das sehe ich nicht, dass iobroker hier auf alle Defizite von anderen Produkten Rücksicht nehmen muss.

Über Heidi-SQL kannst Du relativ einfach einen View erstellen:

[IOMAX 0]

@mickym

Hallo Mickym,

danke für deine schnelle Antwort.
Ich glaube aber, das kann nicht funktionieren.

Ich habe folgende Tabellen:

Datapoints enthält:

Bei den Zeilen 11-17 habe ich mit Aliasen herumgespielt

Sources enthält:

Da ist nach meinem Verständnis nichts Brauchbares drin.
Aber selbst wenn, dann könnte ich damit trotzdem nichts anfangen, denn die Zeilen in "ts_number" völlig ohne
Identifier, sodass ein Mapping nicht möglich ist.

Maxi

[mickym]

@iomax-0 Wieso Du hast doch bei _from immer 0 oder 1?

Ich habe gerade mal so einen View erstellt.

Diese Tabelle enthält nur noch die Daten einer source.

[mickym]

@iomax-0 Im Prinzip - gut bei Deinen Aliases ist das halt nicht so einfach - aber Du siehst doch bei jeder Tabelle, dass über _id eine eindeutige Quelle zugeordnet ist. Ist die Frage, ob es überhaupt Sinn macht hier mit Aliasen zu arbeiten, weil wenn man mal eine andere Source hat, automatisch eine neue ID erstellt wird, so dass man die Abfragen für die Views entsprechend erweitern kann.

Über den View kann man mit dem SQL Statement ja ggf. das flexibel steuern.

Die Tabellen selbst schauen immer gleich aus, die legt ja der SQL Adapter an. Deswegen ist die Struktur der Tabellen bei allen identisch - egal wieviele Punkte. Das ist auch meines Erachtens sinnvoll, um da eine Struktur zu schaffen, die unabhängig von der Art und Anzahl an geloggten Punkten ist.

[mickym]

@iomax-0 sagte in Sensorwerte in SQL-DB schreiben:

Aber selbst wenn, dann könnte ich damit trotzdem nichts anfangen, denn die Zeilen in "ts_number" völlig ohne
Identifier, sodass ein Mapping nicht möglich ist.

Das stimmt nicht - mit dem _id - ist über die datapoints die Quelle eindeutig zuzuordnen. Wenn Du mal zwischenzeitlich das Logging deaktiviert hast und dann wieder aktiviert hast - dann kann es sein, dass vielleicht die datapoints nicht mehr da ist. Bei jeder Aktivierung eines Datenpunktes wird ein neuer Eintrag in der datapoints Table generiert.

Sprich alle Datensätze in Deiner ts_number Tabelle, in der _id = 1 ist, stammen vom Datenpunkt ... consumptionMeterReadingKWh

Sprich mit einem View consumptionMeterReadingKWh kannst Du Dir über einen entsprechenden View mit dem SQL Statement quasi eine eigene Tabelle mit genau einer definierten Quelle aus datapoints erstellen.

[OliverIO]

@iomax-0 sagte in Sensorwerte in SQL-DB schreiben:

kann aber nicht zuordnen, welche Zeile zu welchem Datenpunkt von den 9 gehört

dafür sind datenbanken da, das man hoch performant millionen von datensätze so abrufen kann wie man will.
in iobroker gibt es eine separate tabelle datapoints mit allen datenpunkten die aufgezeichnet werden. der id in dieser tabelle entspricht der Zahl in _from in den Tabellen ts_number/etc,

wenn du sehen willst wie der datenpunkt lautet, dann kann man das über eine query abfragen

diese hier fragt den datenpunktnamen, den timestamp und den wert ab. über die where bedingung wird der id aus datapoint mit dem _from wert aus den ts_number verknüpft
Der LIMIT befehl begrenzt mal auf 100 Datensätze, da unter umständen da ja ganz viele werte in der datenbank gespeichert ist
evtl solltest du auch auf einen zeitbereich begrenzen

SELECT dp.name,val.ts,val.val from ts_number as val,datapoints as dp where dp.id = val._from LIMIT 100

hier ein kleines tutorial zu den datenbank abfragen
https://www.w3schools.com/sql/sql_select.asp

[fastfoot]

_from ist nicht ziehlführend, sondern die id, sie ist mit einer id aus datapoints identisch. Man muss keine view erstellen sondern macht das gleich im sql-statement

SELECT d.id, d.name, FROM_UNIXTIME(n.ts/1000) datum, n.val
FROM ts_number n JOIN datapoints d ON n.id=d.id AND d.name="tankerkoenig.0.stations.5.e5.feed"
WHERE n.ts >= UNIX_TIMESTAMP(DATE('2022-6-1'))*1000
  AND n.ts <  UNIX_TIMESTAMP(DATE('2022-6-2'))*1000;

liefert

id;name;datum;val
1;tankerkoenig.0.stations.5.e5.feed;2022-06-01 09:56:30,2760;1.879
1;tankerkoenig.0.stations.5.e5.feed;2022-06-01 10:41:29,9850;1.879
1;tankerkoenig.0.stations.5.e5.feed;2022-06-01 10:56:30,1020;1.869
1;tankerkoenig.0.stations.5.e5.feed;2022-06-01 12:11:30,0160;1.869
1;tankerkoenig.0.stations.5.e5.feed;2022-06-01 12:26:30,0300;1.899
1;tankerkoenig.0.stations.5.e5.feed;2022-06-01 13:41:30,0650;1.899
1;tankerkoenig.0.stations.5.e5.feed;2022-06-01 13:56:30,0730;1.889
1;tankerkoenig.0.stations.5.e5.feed;2022-06-01 17:11:30,1950;1.889
1;tankerkoenig.0.stations.5.e5.feed;2022-06-01 17:26:30,1200;1.909
1;tankerkoenig.0.stations.5.e5.feed;2022-06-01 17:41:30,1120;1.889
1;tankerkoenig.0.stations.5.e5.feed;2022-06-01 17:56:30,1780;1.889
1;tankerkoenig.0.stations.5.e5.feed;2022-06-01 18:11:30,1100;1.879
1;tankerkoenig.0.stations.5.e5.feed;2022-06-01 18:41:30,2390;1.879
1;tankerkoenig.0.stations.5.e5.feed;2022-06-01 18:56:30,1310;1.859
1;tankerkoenig.0.stations.5.e5.feed;2022-06-01 19:11:30,1260;1.899
1;tankerkoenig.0.stations.5.e5.feed;2022-06-01 19:26:30,1350;1.889
1;tankerkoenig.0.stations.5.e5.feed;2022-06-01 20:41:30,1900;1.889
1;tankerkoenig.0.stations.5.e5.feed;2022-06-01 20:56:30,1740;1.859
1;tankerkoenig.0.stations.5.e5.feed;2022-06-01 21:59:11,8280;1.859

[mickym]

@fastfoot Ja OK - Du hast Recht - sorry mein Fehler. Ich verbessere das mal.

In den Screenshots hatte ich es ja richtig gemacht - nur hier falsch beschrieben.
Finde das immer toll, wenn man das so aufmerksam mit liest. Vielen Dank an @fastfoot

@fastfoot sagte in Sensorwerte in SQL-DB schreiben:

Man muss keine view erstellen sondern macht das gleich im sql-statement

Ja das habe ich ja auch gesagt, nur der TE meinte ja das Grafana wohl nicht mit SQL Statements umgehen kann, sondern man quasi nur Tabellen als Ganzes als Source angeben kann. Da kann ich aber nichts zu sagen, weil ich Grafana nicht verwende.

[stefank22]

Man kann in Grafana sowohl SQL-Statements eingeben, als auch eine Queryeditor verwenden.

Würde mich auch mal interessieren, wie man das löst, bin in SQL aber nicht fit genug.

Was müsste man denn konkret in Grafana hinterlegen, bzw. wie sähe das SQL-Statement aus,
wenn man beispielsweise die Werte (Tabelle ts_numbers) der Geräte-ID 10 (Tabelle datapoints) selektieren möchte ?

[IOMAX 0]

Hey Leute,

ihr habt Recht. Über die ID ist das ja eindeutig zuordenbar !!! Ist schon spät
Brauche quasi nur nen SQL-"SVERWEIS" zu machen.

Wie das geht, weiß ich aber nicht. Schließe mich daher Stefank22 an ..

Grüße gehen raus !

[mickym]

@stefank22

SELECT  `id`,  `ts`, `val` FROM `iobroker`.`ts_number` WHERE `id` = 10 

OK Du kannst noch ORDER by ts einfügen.

Und noch ein Fehler meinerseits - die Datenpunkte stehen in Tabelle datapoints. Source enthält die Quelle, die den Datenpunkt beschrieben hat.

Sorry für die Verwirrung.

Also
datapoints - enthält die Quelle also den Datenpunkt
source - ist die ID die den Datenpunkt beschrieben hat. (Also meist die Adapterinstanz).

Sorry für die Verwirrung, die ich vielleicht gestiftet habe.

[IOMAX 0]

Ha, ich habs. Das ist ja doch einfacher, als gedacht.

SELECT
ts AS "time",
val
FROM ts_number
WHERE
id = 8
ORDER BY ts

Über die ID kann ich nun schön alle Datenpunkte ansteuern.

DANKE EUCH ALLEN !