Ich nutze für meine Heimautomatisierung den IOBroker. Für die Historisierung von Status-Werten verwendet ich Postgresql. Für die Visualisierung Grafana. Im Zusammenspiel Grafana / iobroker gibt es das Problem, dass iobroker die timestamps in ms speichert, grafana aber nur Sekunden oder Nanosekunden kennt. Ich habe das bis jetzt so gelöst, dass ich den timestamp durch 1000 teile um auf Sekunden zu kommen. Die Performance ist aber saumäßig: statt 60 ms dauert eine einfache query 8 Sekunden. Die würde ich gerne verbessern und denke da an einen Insert Trigger: Immer wenn ein neuer Wert geschrieben wird, soll der ms Wert in Sekunden umgerechnet und in ein zus. Attribut gespeichert werden.
Die Tabelle ist wie folgt aufgebaut:
ts_number (Tabelle)
ts (timestamp der Änderung in ms (bigint)
val (der wert des Objektes)
id (ID des Objektes
ts_sec (zu berechnender timestamp in sekunden (bigint)
Habt Ihr mir dazu einen Tip oder am besten gleich den SQL Code ?
Falls Sekunden nicht gehen (es könnten Kommawerte entstehen) geht für mich auch Umrechnung in Nanosekunden.
Vielen Dank für die schnelle Antwort. Die erste Variante hat grundsätzlich funktioniert. Leider war die Performance auch sehr schlecht, praktisch keine Verbesserung.
Die 2te hat nicht getan. Ich bekomme:
ERROR: FEHLER: Funktion pg_catalog.extract(unknown, bigint) existiert nicht
HINT: Keine Funktion stimmt mit dem angegebenen Namen und den Argumenttypen überein. Sie müssen möglicherweise ausdrückliche Typumwandlungen hinzufügen.
SQL state: 42883
Anbei auch noch, falls es hilft, die Queries. Die obere mit der generated column läuft ca. 7 Sek, die untere auf der original spalte nur 50 ms. Indexes sind gesetzt auf id, ts und ts_sec
SELECT
ts_sec AS "time",
val AS "Verbrauch"
FROM ts_number
WHERE
ts_sec >= 1666715215 and ts_sec <= 1666726015 and id = 4
ORDER BY 1 DESC;
SELECT
ts AS "time",
val AS "Verbrauch"
FROM ts_number
WHERE
ts >= 1666715215000 and ts <= 1666726015000 and id = 4
ORDER BY 1 ASC
Hast du noch andere Ideen? Sonst wie würde es mit dem Trigger aussehen?
Das mit dem timestamp habe ich tatsächlich übersehen. Aber unabhängig davon wollte ich die spalten ts, id, und val nicht anpassen, weil die vom IOBroker kommen und ich nicht weiß, was das für Auswirkungen hat.
ABER: es funktioniert jetzt dennoch wie erwartet. Ich glaube der Tip mit dem vielen Indexes war’s Ich hab den Index auf ts entfernt und jetzt läuft alles wie erwartet und hoch performant.