Sensorwerte in SQL-DB schreiben

[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 !