SQL Zeit der letzten Veränderung in einer Tabellenspalte herausfinden

[an01662]

Hallo,

Ich habe eine Wetterstation, welche Meßwerte minütlich in eine MYSQL Datenbank einträgt.
Mein Problen ist der Zähler vom Regenmesser dieser schreibt seinen Wert in eine Spalte. Wenn es nun eine Zeit lang nicht regnet, dann verändert sich auch der Wert in der Spalte nicht. Wie kann ich nun herausfinden, wenn das letzte mal ein Wert geändert wurde?
Ein Zeitstempel ist in der Datenbank bei jedem Eintrag hinterlegt.
Hat jemand eine Idee?

Grüße André

 

[up.whatever]

Differenz zum Wert der vorherigen Zeile bilden und dann auf Differenz > 0 filtern.
Siehe https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_lag

 

[RalphS]

Setze einen Trigger ON AFTER INSERT / UPDATE (je nachdem was Du brauchst) und sag dort, daß der Zeitstempel in der Spalte auf CURRENT_TIMESTAMP aktualisiert werden soll.

IIRC kann MySQL auch in der Spaltendefinition ON UPDATE oder sowas in der Art. Müßtest Du mal gucken. Das ist aber nonstandard.

 

[G-Red]

Ich weiß nicht ob MySQL die Max Funktion hat. Aber wenn, dann dürfte der SQL ausdruck in etwa so aussehen

SELECT MAX( timestamp ) FROM my_table

 

[an01662]

Danke für die Vorschläge. Werde damit mal etwas herumexperimentieren.

 

[Andreas_]

@G-Red
Wenn die Werte minütlich geschrieben werden, dann bekommst Du mit MAX zwar den letzten Eintrag, das dürfte aber in der Regel nicht der Zeitpunkt der letzten Änderung des Wertes des Regenmessers sein.

Ein "ON INSERT" Trigger sollte funktionieren, bei dem der aktuell geschriebene Wert mit dem vorhergehenden verglichen wird und entsprechend ein Wert in einer neuen Spalte gesetzt wird. Ist vermutlich auch die schnellste Lösung für die Abfrage. In der neuen Spalte hast Du dann eine Kennzeichnung aller Datensätze, bei denen sich der Wert geändert hat. Darüber kannst Du dann mit MAX() abfragen.

Ansonsten kannst Du die Tabelle mit sich selbst über den Timestamp joinen (Timestamp mit Timestamp - 1 min, eventuell runden, wenn der Timestamp eine höhere Genauigkeit aufweist) und dann die beiden Spalten mit den entsprechenden Werten vergleichen.

Ich würde vermutlich den schon von RalphS vorgeschlagenen Trigger nutzen. Die andere Lösung wird bei einer großen Anzahl von Datensätzen deutlich mehr Ressourcen benötigen.

 

[mental.dIseASe]

Ich würde das auch mit Analytical Functions machen, wie von up.whatever vorgeschlagen. Dann spart man sich auch den Self-Join. Ich wusste gar nicht, dass MySQL das auch kann. Ziemlich cool.

 

[Makkaroni]

Am elegantesten finde ich die Triggerlösung. Die Verifiierung ala Task (z.B. jede Minute) würde Ressourcen benötigen, die vermeidbar sind. Greta würde die Triggerlösung favorisieren!

 

[parats]

Du schreibst bereits das Datum mit, oder verstehe ich was falsch? Hast du pro Sensor einen Eintrag (Stammdaten) oder schreibst du fortlaufend in die Tabelle (Bewegungsdaten)?

 

[an01662]

Ich schreibe fortlaufend die Werte der Sensoren in eine Tabelle und in eine Spalte den Timestamp.
Das sieht dann als Beispiel so aus.

"5721" "2,73" "0,12" "84,95" "1034,94" "-34,79" "0" "0,02" "254" "6" "0,2" "0,25" "3,74" "0,23" "2020-01-01 16:51:50"
"5720" "2,63" "0" "85,14" "1034,91" "-34,56" "0" "0,02" "254" "6" "0" "0,15" "3,74" "0,27" "2020-01-01 16:50:50"

Für die Idee mit den Analytical Functions muss ich erst mal mein Ubuntu 18.04 LTS updaten, denn da läuft noch MYSQL 5.7. Das mache ich aber erst mit dem Umstieg auf 20.04 LTS.

 

[parats]

Pro Sensor eine Zeile?
Ansonsten kannst Du doch einfach die Daten pro Sensor abfragen und entsprechend deiner Erwartung mit einem Offset auf das Max und dem Delta arbeiten. Also alle Werte gruppiert nach Sensor mit MAX Datun älter Tag X. Oder verstehe ich deine Frage inhaltlich falsch?

 

[Andreas_]

Ist doch im Eingangsposting imho gut beschrieben. Ein Sensor schreibt jede Minute einen Eintrag, also auch wenn der Wert sich nicht ändert. Er möchte jetzt den Eintrag finden, bei dem sich der Wert des Sensors das letztemal geändert hat.

Prinzipiell geht das auch mit MAX:

SELECT MAX(timestamp) FROM Sensordaten
WHERE wert != (SELECT wert FROM Sensordaten WHERE timestamp = (SELECT MAX(timestamp) from Sensordaten))

/edit: Wenn mehrere Sensoren in eine Tabelle schreiben, dann muss man natürlich den Sensor in die WHERE - Bedingung mit aufnehmen.

SELECT MAX(timestamp) FROM Sensordaten
WHERE sensorId={input} AND wert !=
(SELECT wert FROM Sensordaten WHERE sensorId={input} AND timestamp =
(SELECT MAX(timestamp) from Sensordaten WHERE sensorId={input}))

Alternativ kann man an Stelle der Subselects auch mit Joins arbeiten.

 

[an01662]

Es kann so einfach sein. Funktioniert bestens.
Besten Dank für die Idee.

Ist doch im Eingangsposting imho gut beschrieben. Ein Sensor schreibt jede Minute einen Eintrag, also auch wenn der Wert sich nicht ändert. Er möchte jetzt den Eintrag finden, bei dem sich der Wert des Sensors das letztemal geändert hat.

Prinzipiell geht das auch mit MAX:

SELECT MAX(timestamp) FROM Sensordaten
WHERE wert != (SELECT wert FROM Sensordaten WHERE timestamp = (SELECT MAX(timestamp) from Sensordaten))