Easier way to remove bad values from history? SQL?
Bad data, it happens to us all. Doing some testing or a sensor fails and goes out of range - but not far enough to be discarded.
This can completely mess up the trending display, as it is doing to me right now. Even when rejecting extreme values, sometimes a glitch can return a value that is not extreme enough to be discarded, but well out of the normal operation of the process resulting in the scale being thrown off every time you try to view historical data.
I have done the export/reimport CSV thing before but this is honestly a very clumsy and slow way of editing the history, and the interface has changed somewhat to the point where I had to hand-edit my CSV last time I did it.
Is there an easier way to do this with an SQL query? I have tried modifying the pointvalues table, where the bad data point seems to be easily found with SELECT * FROM pointvalues WHERE pointvalue = $baddata;
However UPDATEing this value does not seem to result in anything changing on the charts? Is it cached somewhere or stored in another location?
MattFox last edited by
This wholly depends on if you're using the SQL database to store values or the NoSQL database because it's on mango unit or a cloud installed setup.
I'd wager you're using the NoSQL system which means you're gonna have to export, open with notepad, copy and paste the data to maintain the correct date-time format (excel likes to screw with it) then filter by the value column.
With all of your filtered values, append the delete value in the final column, save, and reimport values to delete all unwanted values.
Also note you can change your datapoint properties to filter and ignore extreme/unwanted values so they won't go into your system.
Hope this is of some help