InfluxDB does not yet support subqueries or having clauses, or temporary values. A calculated value in the SELECT clause cannot be used in the WHERE clause. In both cases, "ch" was interpreted as a tag that doesn't exist.
Instead you want to use continuous queries <https://docs.influxdata.com/influxdb/v0.13/query_language/continuous_queries/> to cache the initial results, and then query those. First set up a CQ to produce the full result list and store it in a new measurement: CREATE CONTINUOUS QUERY foo ON mydb BEGIN SELECT DIFFERENCE("vieBurnerFlame") as ch INTO new_measurement FROM "gInfluxTemperatures" GROUP BY time(5m) END Then query for just the non-zero results: SELECT * FROM new_measurement WHERE ch != 0 There may be an issue with the DIFFERENCE function and the GROUP BY time(5m) interval. I'm honestly not quite sure how that will behave because we can't see the original source data. On Tue, Aug 30, 2016 at 8:12 AM, Fulvio Spelta <[email protected]> wrote: > Hi all, I'm a newbie of SQL and influxdb; me and a colleague are trying to > create a montoring system of field data such as temperatures, energy and so > on, in order to work on some real use cases and test the use and learn the > basis of influxdb and grafana. > > We are quite satisfied with the basic monitoring so we are going to go > further in learning and we like to use influx SQL to analyze the collected > data and we are experiencing our first "wall". > > Use case > we have a field with values only 0/1, that represent, for example, the > presence of a flame in a burner (burner ON/OFF) that is stored every 5 min. > > We'd like to find the exact time where the flame was turned ON/OFF so we > have tested this query: > SELECT DIFFERENCE("vieBurnerFlame") as ch FROM "gInfluxTemperatures" WHERE > (time >= now() - 1d) > > and the result (frgmer follow) is ok, you cas see ch calculated field as > 1/-1 as ON FLAME/OFF FLAME: > time ch > 2016-08-29T17:15:00.201Z 0 > 2016-08-29T17:20:00.185Z 1 > 2016-08-29T17:25:00.201Z 0 > 2016-08-29T17:30:00.232Z -1 > 2016-08-29T17:35:00.216Z 0 > > Further i've tryed to filter out only the "event", referring to previous > example the idea is to have only the 2 points: > 2016-08-29T17:20:00.185Z 1 > 2016-08-29T17:30:00.232Z -1 > > so i've used the query: > SELECT DIFFERENCE("vieBurnerFlame") as ch FROM "gInfluxTemperatures" WHERE > (time >= now() - 1d) and ch <> 0 > > but the results set was the same. The opposite test: > SELECT DIFFERENCE("vieBurnerFlame") as ch FROM "gInfluxTemperatures" WHERE > (time >= now() - 1d) and ch = 0 > returns no data (as expected from previous result). > > I kindly ask why i'm getting this result and how to achieve the gola. > Thank a lot for support. > f > > -- > Remember to include the InfluxDB version number with all issue reports > --- > You received this message because you are subscribed to the Google Groups > "InfluxDB" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at https://groups.google.com/group/influxdb. > To view this discussion on the web visit https://groups.google.com/d/ > msgid/influxdb/afabcec4-600e-4860-a7bb-9dcb1ef56ab5%40googlegroups.com > <https://groups.google.com/d/msgid/influxdb/afabcec4-600e-4860-a7bb-9dcb1ef56ab5%40googlegroups.com?utm_medium=email&utm_source=footer> > . > For more options, visit https://groups.google.com/d/optout. > -- Sean Beckett Director of Support and Professional Services InfluxDB -- Remember to include the InfluxDB version number with all issue reports --- You received this message because you are subscribed to the Google Groups "InfluxDB" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/influxdb. To view this discussion on the web visit https://groups.google.com/d/msgid/influxdb/CALGqCvMkNd9VnSQ0oBy2BtHLV7N65VaWBZHXhQNGm7ayZ7B0eg%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.
