How to get time($timeFilter) in SELECT query with InfluxDB as the data source?

Grafana InfluxDB
1

I have an InfluxDB data source and I am creating dashboard where the metric is calculated as the percentage. Sample query is as shown below.

SELECT cumulative_sum(mean(“metricA”))*100/43200 FROM “measurementA” WHERE $timeFilter GROUP BY time(60s) fill(null)

Here, the divisor 43200 is the number of minutes in 30 days and correspondingly, the dashboard time is set to now()-30d. If I change the time period on the dashboard to a different period, say 15days, then this calculation would give an incorrect value.

Thus, in order to resolve this, I am looking for a solution which allows me to write query something like this.

SELECT cumulative_sum(mean(“metricA”))*100/$time_in_mins($timeFilter) FROM “measurementA” WHERE $timeFilter GROUP BY time(60s) fill(null)

Please let me know if there is any solution/workaround for this.

2

Hi @amateurviz1 and welcome to the Grafana forum.

The only way that I know how to do this is to use Flux. You are currently using InfluxQL. If you are not invested too far into writing your queries using InfluxQL and this type of query is important enough, you should be able to change from InfluxQL to Flux. Here and here are some potentially helpful links.

In Flux, assuming you are using nanosecond precision, I believe you can create a new column by converting the timeRangeStart and timeRangeStop timestamps to int, subtracting, and then converting from nanoseconds to hours:

from(bucket: "Bucket1")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "your-measurement")
  |> filter(fn: (r) => r["_field"] == "your-field")
  |> map(fn: (r) => ({  r with PeriodTime:  float(v: uint(v: v.timeRangeStop) - uint(v: v.timeRangeStart))/float(v:3600000000000)}))
3

Thanks @grant2 for your answer.

As of now, I am looking for a solution with InfluxQL. Please let me know if there is any.

I am trying with using custom variables for fixed number of durations like 1h, 1d, 7d etc, however, I would still need a function to get the number of hours from this variable. If there is any other function for this, please let me know.