Grafana / MariaDB: Aus 2 Queries 1 machen?

[bfit]

Ich nutze den SQL-Adapter, um aus dem ioBroker Messwerte in eine MySQL/MariaDB zu schreiben. Zwei dieser Messwerte möchte ich nun gerne in Grafana in einem Scatter-/XY-Chart visualisieren.

Mein Problem: Der SQL-Adapter schreibt die Werte mit verschiedenen IDs (je Objekt) in die selbe Tabelle ts_number. Als z.B. den Messwert Außentemperatur mit der ID 14

id  ts                   val
14  2022-11-13 23:59:59  4.42
14  2022-11-14 23:59:59  5.88
14  2022-11-15 23:59:59  6.30

und den Messwert Heizenergie mit der ID 25

id  ts                   val
25  2022-11-13 23:59:00  23
25  2022-11-14 23:59:00  24
25  2022-11-15 23:59:00  16

Wenn ich in Grafana mehrere Messwerte in einem Zeitreihen-Chart anzeigen wollte, habe ich mir bisher einfach damit beholfen, dass ich zwei Queries gemacht habe.Für die Außentemperatur z.B.

SELECT
  ts AS "time",
  val AS "Außentemperatur"
FROM ts_number
WHERE
  id = 14
ORDER BY ts

und analog noch mal einen für die Heizenergie mit id = 25.

Das geht beim XY-Chart aber leider nicht, dieser Panel-Typ benötigt wohl beide Werte aus einem Query bzw. im Ergebnis in einer Datentabelle, die so aussieht:

ts           Temp   Energie
2022-11-13   4.42   23
2022-11-14   5.88   24
2022-11-15   6.30   16

Der Query müsste also beide IDs zusammenbringen und gleichzeitig den Zeitversatz der beiden Objekte von jeweils 59 Sekunden (durch eine Gruppierung?) ausbügeln.

Leider stehe ich auf dem Schlauch: Wie müsste der entsprechende Query aussehen?

[Codierknecht]

Soll das tatsächlich nur tagesgenau sein?
Werden die Werte nur 1x/Tag geschrieben? Oder muss da noch Summe/Durchschnitt oder so berechnet werden?

[bfit]

@codierknecht ja, das soll tatsächlich nur tagesgenau sein – die Werte werden nur einmal täglich zu den genannten Uhrzeiten von verschiedenen Adaptern/Scripts erzeugt.

[Codierknecht]

@bfit

SELECT A.ts, A.val AS WERT1, B.val AS WERT2
  FROM ts_number A 
 INNER JOIN ts_number B ON date_format(from_unixtime(B.ts/1000), '%Y-%m-%d') = date_format(from_unixtime(A.ts/1000), '%Y-%m-%d') AND B.id = 25
 WHERE A.id = 14

[bfit]

@codierknecht Vielen Dank!

Wenn ich es direkt so übernehme, spuckt Grafana direkt folgende Meldung unter dem Query Editor aus:

db has no time column: no time column found

Streiche ich das "/1000" aus dem Join, zeigt er mir im Table View immerhin die Spalten richtig an, nur leider noch ohne Daten

Woran könnte das liegen?

[Codierknecht]

@bfit
Das "/1000" darfst Du an der Stelle nicht entfernen.
Das "ts" ist ein timestamp - dummerweise mit Millisekunden. Damit kommt MySQL nicht klar um ein Datum auszurechnen.
Das wird aber gebraucht um die beiden Tabelleneinträge in Beziehung zu setzen.

Ich schau mal in Grafana ...

[Codierknecht]

@codierknecht

SELECT A.ts AS "time", A.val AS WERT1, B.val AS WERT2

Liefert bei mir zumindest mal Daten.

[bfit]

@codierknecht Mit

AS "time"

hat es nun auch geklappt – vielen Dank!!

[OliverIO]

@bfit sagte in Grafana / MariaDB: Aus 2 Queries 1 machen?:

SELECT
  ts AS "time",
  val AS "Außentemperatur"
FROM ts_number
WHERE
  id = 14 OR id = 25
ORDER BY ts

und wenn man in die Abfrage den ID des anderen Datenpunkts mit einschließt?

[Codierknecht]

@oliverio
Dann hast Du jeweils 2 Zeilen, weil die Timestamps unterschiedlich sind.