Filtering on integer range

Question

We're running a Java service, logging metrics with Micrometer to InfluxDB and using Grafana to visualize it. Micrometer only supports string tag/values, but I've stored an integer as a string value, query_span and want to filter on that in Grafana.

This is what I've point-n-clicked myself to in Grafana:

SELECT sum("value") FROM "db_read"
WHERE ("short_name" = 'app' AND "environment" =~ /^$site$/
        AND "query_span" > '1' AND "query_span" <= '7')
        AND $timeFilter
GROUP BY time(5m) fill(null)

The query_span part does not yield any data. Dropping the single-quotes doesn't work either. When I set the lower part of the interval to "query_span" >= '2' instead, I get some hits. Which leads me to believe some kinda string-to-string compare is going on (and the 'equal' part of 'greater or equal' returns true when =="2"). I see Influx supports casting selected fields, bit I don't understand how to cast in the WHERE clause (naïve attempt failed).

Note: this is not a clone of this, distinct, Grafana/InfluxDB question. (Oh do I miss the good ol' days where these disclaimers were superfluous! :)

Edit: regex filtering works but is not ideal.

score 1 · Accepted Answer · answered Dec 05 '20 at 09:23

Of course regexp will be working with string, but math comparison >,<,<=,>= doesn't - you need float/int type for that. If you use <,< on the the strings, then you will get lexicographic order. See: https://docs.influxdata.com/influxdb/v2.0/reference/flux/language/operators/#string-operators

I would say regexp is the best option for your use case (if you have low cardinality). You need to define allowed range explicitly, e.g. 4-9:

"query_span" =~ /^[4|5|6|7|8|9]$/

But of course proper InfluxDB schema with floats will be the best option.

Filtering on integer range

1 Answers1