Dynamic Time Range in Queries?

Hello,

I am using Grafana with InfluxDB as my data source inside Home Assistant, but I am having trouble trying to do some math between queries. What I’m trying to do is find the percent difference between the avg values of two queries, over a dynamic time range. As you can see below, I’ve managed to accomplish this exact thing, except it’s over an absolute time range. In this case, it’s giving me the percent difference between the average wind speed over the last 7 days vs. the 7 days before that.

Unfortunately, this is not all that helpful, as I would like to be able to compare only the current week so far to the completed week before it. What I have now is comparing just the last 7 days to the 7 days before that, which is not what I want. However, I can’t seem to find a method to allow me to compare this week to last week.

I know Grafana has time ranges like “now/w” which is “this week thus far”, but I am not able to put those specific time ranges into the query - it doesn’t work. Is there a way to do what I want?

Hello @Kackerlacka,
Manipulation timestamps with InfluxQL is quite hard. This would be very achievable with flux though.
https://docs.influxdata.com/influxdb/v2.0/reference/flux/stdlib/date/#days-of-the-week
https://docs.influxdata.com/influxdb/cloud/query-data/flux/manipulate-timestamps/

Are you running 1.8 and can you enable Flux? If so I’m happy to provide some sample Flux.

I am able to use Flux for this, I believe. Could you please provide some code and I will try to see if I can get it to work? Thanks!

Hello @Kackerlacka,
Youll want something like this:

from(bucket: "Air sensor sample dataset")
  |> range(start: -2w, stop: now())
  |> filter(fn: (r) => r["_measurement"] == "airSensors")
  |> filter(fn: (r) => r["_field"] == "humidity")
  |> filter(fn: (r) => r["sensor_id"] == "TLM0100")
  |> window(every: 1w)
  |> mean() 

Or course your bucket and measurement and tags and fields will be different

Quick question…So I know this is probably more of a Grafana question rather than an InfluxDB question, but I did try asking there and they told me to come here. So I’m looking to make this “dynamic” using Grafana’s time ranges.

As you probably know there is a time range picker in the Grafana UI where you can choose things like “this month”, “last month”, “this year”, etc.

I’m wondering if it’s possible to make this Flux code dynamic so that, for example, if I choose “this month” in the UI, it will change my query to compare the avg of all values this month vs. the avg of all values last month. And, for another example, if I choose “this week” it will compare this week to last.

Ideally I’d like to be able to even choose custom days (since Grafana’s time range picker let’s you choose a custom range of days), like, say if I choose the 15th of this month to the 28th, it’ll compare that to the 15th of last month to the 28th.

Is this even possible?