I have a measurement which has multiple fields and often data for only several but not all of the fields is provided, and only the received data is inserted leaving many of the fields with NULL value.
Data for fields F4 and F5 was accidentally swapped and written to Influx between 2019-08-18 and 2019-08-25.
My originally attempt used a PHP script to manually go through the records, however, was definitely the wrong approach. I first performed the following query to get all timestamps which had the bad data.
SELECT F4, F5 FROM MyMes WHERE time>="2019-08-18T00:00:00Z" AND time<="2019-08-25T00:00:00Z"
Then, for each of the timestamps, I performed the following to get all fields that had a value for the given timestamp:
SELECT * FROM MyMes WHERE time>="2019-08-18T00:00:00Z" AND time<="2019-08-25T00:00:00Z"
I then “fixed” the data and wrote it back. For instance, if the previous query returned fields F1, F3, and F4, I would write back F1, F3, NOT F4, and F5 where F5 used the returned F4 value.
I think I did not understand how Influx writes data, and while I think I now know, would like to confirm. If I have an existing values for some fields fir a given timestamp, and then I write to another field for that timestamp, Influx will leave the existing fields as they are, correct?
Is the following what I should have done?
SELECT F1, F2, F3, F4, F5 INTO MyMes2 FROM MyMes WHERE time<"2019-08-18T00:00:00Z" GROUP BY * SELECT F1, F2, F3, F4 AS F5, F5 AS F4 INTO MyMes2 FROM MyMes WHERE time>="2019-08-18T00:00:00Z" AND time<="2019-08-25T00:00:00Z" GROUP BY * SELECT F1, F2, F3, F4, F5 INTO MyMes2 FROM MyMes WHERE time>"2019-08-25T00:00:00Z" GROUP BY * DROP MEASUREMENT MyMes SELECT * INTO MyMes FROM MyMes2 GROUP BY * DROP MEASUREMENT MyMes2 select min(P95), max(P95), min(P98), max(P98) FROM L2
FIXING MY ORIGINAL GOOF
F4 and F5 have very different values and one can detect using less than and greater than. Is it possible to do some sort of SELECT/INTO query which only copies the data to a new measurement if within bounds?