What I have is a table containing some temperature readings which often jump back and forth.
+------------+----------+-------------+ | timestamp | sensorID | temperature | +------------+----------+-------------+ | 1511020195 | 1 | 25 | | 1511020196 | 1 | 26 | | 1511020197 | 1 | 25 | +------------+----------+-------------+
I’d like to iterate through the table and delete some rows if some conditions are met. Here is some pseudocode of what I am trying to do:
int senID = 1; int timestamps[] = //SELECT `timestamp` FROM `temperature` WHERE `sensorID` = senID int actentry = sizeof(timestamps); while(actentry >= 2) { int acttemp = gettemp(actentry); int lasttemp = gettemp(actentry-1); int secondlasttemp = gettemp(actentry-2); if(acttemp == lasttemp) { //DELETE FROM `temperature` WHERE `timestamp` = timestamps[actentry] AND `sensorID` = senID } else if((acttemp == secondlasttemp) && ((acttemp == lasttemp+1) || (acttemp == lasttemp -1))) { //DELETE FROM `temperature` WHERE `timestamp` = timestamps[actentry] AND `sensorID` = senID //DELETE FROM `temperature` WHERE `timestamp` = timestamps[actentry-1] AND `sensorID` = senID } actentry--; } int gettemp(int entry) { return //SELECT `temp` FROM `temperature` WHERE `timestamp` = timestamps[entry] AND `sensorID` = senID }
Is there a way of executing this directly on my MySQL DB?
I asked before but didn’t get an answer. Because of that I wrote this pseudocode to give a better explanation of what I am trying to to.
Thank you for your answers in advance!