Continuous Query to extract threshold crossing times?

Hello, is it possible please to use CQs to look through the data row by row and pull out the times when the value went above a value, and then also the times it went back down to zero?
For example, with a current/power trace I’d like to be able to easily see when a device was switched on/off.

If you are running these queries for shorter duration e.g. 1min or so. One possible way to find the answer is using the CQ looking for MAX and MIN values and multiplying them under it. e.g. below

select max(field) * min(field) into temp from table into where time <= ‘’ and time >= ‘’ group by time(1m)

But it requires populated values in the original table else it can lead to false positives.

1 Like

Thank you for the tip, I’m very new to Influx so I’m reading up the docs to understand more.

Currently I’m trying out storing the state of the product (on/off) as a tag along with the power data.

I’m also then detecting the switch on/off at source and when one occurs sending a separate event to a different measurement in Influx to build up a table of on/off times. Struggling bit to see what should be a tag and a field in that scenario, as it is really on the time that I want (i.e. Don’t need a field value).

Does this sound reasonable? Thanks for any tips!

@marv_mcd Booleans for field values are very space efficient. I would suggest adding the device state (T/F) as your field.