On a measurement in my database I have two fields. One is the Value and the other is the Value Quality. Value is a double and Value Quality is either a Boolean or int (0 or 1) if int. When the values quality turns to 0 or false, the value is no longer written to Influx however, the quality points will be, even if they are 0’s. I would like to be able to use aggregates on the Value (min max last start) while while returning that values quality point as well (value and value quality will have the same timestamp). A join statement is the only way I can find to do this however with large datasets the join statement takes over 1 min to complete which will not work. I’ve also noticed that this is not an issue in Influx 1.8. When I query using InfluxQL I can get the data I need back within a second. I essentially want Value and Value Quality in two separate columns. InfluxQL on InfluxDB 1.8 formats the data this way, but 2.0 doesn’t seem to do that.
Example flux query (takes 30 seconds to return):
Value=from(bucket: “Test”)
|> range(start: 2022-01-26T05:00:00.0000000Z, stop: 2022-01-27T05:00:00.0000000Z)
|> filter(fn: (r) => r._measurement == “TotalPower_Raw_AltBadQlty.Raw” and r._field == “Value”)
|> drop(columns: ["_start", “_stop”, “_measurement”])
Quality=from(bucket: “Test”)
|> range(start: 2022-01-26T05:00:00.0000000Z, stop: 2022-01-27T05:00:00.0000000Z)
|> filter(fn: (r) => r._measurement == “TotalPower_Raw_AltBadQlty.Raw”
and r._field == “ValueQualityIsGood”)
|> drop(columns: ["_start", “_stop”, “_measurement”])
join(tables: {Quality: Quality, Value: Value}, on: ["_time"], method: “inner”)
Example InfluxQL query (takes less than a second to return):
SELECT Value, ValueQualityIsGood FROM “ScadaRTUTest”.“autogen”.“TotalPower_Raw_AltBadQlty.SwingDoorCompress” WHERE time > :dashboardTime: AND time < :upperDashboardTime:
