Query InfluxDB with CASE or IF statement?

InfluxDB 2 Dashboards
, , ,
1

I am gathering metrics from my philips hue bridge and putting them into an InfluxDB. I’d like to be able to display some of these metrics on a dashboard.

A basic query looks like this:

SELECT mean("state.bri") AS "mean_state.bri" FROM "telegraf_hue"."autogen"."hue" WHERE time > :dashboardTime: AND "lamp"='Couch' GROUP BY time(:interval:) FILL(null)

This returns a nice graph that shows the brightness level of the light.

Basic Query Results

The problem with this query is that the light isn’t actually on all of that time. The Hue Bridge returns the last brightness level, even if it’s off.

I can see if it is on by checking "state.on" for a true or false value.

SELECT mean("state.bri") AS "mean_state.bri" FROM "telegraf_hue"."autogen"."hue" WHERE "state.on" = true AND time > :dashboardTime: AND "lamp"='Couch' GROUP BY time(:interval:) FILL(null)

This only returns points where the light is on.

Query where light is on results

What I’d like to do something like a CASE statement like in MySQL:

SELECT CASE WHEN "state.on" = true THEN mean("state.bri") ELSE 0 END FROM "telegraf_hue"."autogen"."hue" WHERE time > :dashboardTime: AND "lamp"='Couch' GROUP BY time(:interval:) FILL(null)

This doesn’t work. It fails with:

Request failed with status code 400

How can I accomplish something similar with InfluxDB? Ultimately, I’ll be building the dashboards in Grafana, if that matters.

1 Like
2

Hi @Lonestar , welcome ,

have you found a solution ?

best regards

3

@MarcV I have not found a solution.

4

Hi! I know this post is old but just in case it’s helpful to you (or someone else in the future):

For your particular case, you could limit your query to only retrieve results when the state is set to ‘on’. (SELECT ... FROM ... WHERE "on" = true GROUP BY ...)

From there, you can replace your fill(null) with fill(0), which should result in the brightness metric reporting 0 on the graph when the state is not set to on, and returning the brightness value from the query when the state is set to on.

The success of this will depend on your time grouping setting and how frequently data is being pushed to your measurement. If the data is too infrequent or if your time grouping is too small, then the graph might suffer from on-off-on-off syndrome.

I hope this is in the ballpark, and it’s helpful to someone!

  • Dan