Hi folks
how can i count the number of days in the time range that i select.I need that count for calculation in a other query. it would be great if someone can provide me an flux example
Thanks
Welcome
Check this out
Welcome @simpixo1
If I understand your question correctly, you have a query that uses the time range picker in Grafana and you want to use Flux to determine how many days were selected. I believe this would work:
import "date"
startRange = date.truncate(t: v.timeRangeStart, unit: 1d)
endRange = date.truncate(t: v.timeRangeStop, unit: 1d)
daysInRange = uint(v: endRange) - uint(v: startRange)
from(bucket: "your-bucket")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "your-measurement")
|> filter(fn: (r) => r["_field"] == "your-field-name")
|> last() // I put this line in so that the query returns just one record instead of many records
|> map(fn: (r) => ({ r with _value: uint(v: endRange) - uint(v: startRange) }))
|> toInt()
|> map(fn: (r) => ({ r with _value: r._value / 86400000000000 })) // converts integer in nanoseconds to days
|> yield(name: "whatever")
In Influx Data Explorer:
30 days selected:
7 days selected:
There are probably easier ways to do this, but using the startRange and endRange in a map calculation is what came to my mind.
1 Like
You are my hero! It works how I need it 
hi Grant
i have ask you again how can i use the result of you query in another one. I am flux beginner and i do not found the right way
this is my first query. with that i get the whole energy consumption in the choosed time range. in my case “this year”
import "timezone"
option location = timezone.location(name: "Europe/Berlin")
data = from(bucket: "iobroker")
|> range(start: v.timeRangeStart, stop:v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "Stromzaehler[total]")
|> filter(fn: (r) => r["_field"] == "value")
first = data
|> first()
|> set(key: "_field", value: "delta")
last = data
|> last()
|> set (key: "_field", value: "delta")
union(tables:[first, last])
|> difference()
now i want to divide my result with the result of your query, that i get a avarge per day. hope you understand what i want to achive with this 
Thanks
Cheers
simpi
Hi @simpixo1
I do not have much time, but my feeling is that you need to do an inner join. Normally I would just create two tables and start experimenting with the inner.join function to get the result I want. @fercasjr is a real pro at this, so maybe he can check the very last lines below. Something like this:
// query for first table stream starts here
import "timezone"
option location = timezone.location(name: "Europe/Berlin")
data = from(bucket: "iobroker")
|> range(start: v.timeRangeStart, stop:v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "Stromzaehler[total]")
|> filter(fn: (r) => r["_field"] == "value")
first = data
|> first()
|> set(key: "_field", value: "delta")
last = data
|> last()
|> set (key: "_field", value: "delta")
t1 = union(tables:[first, last])
|> difference()
// query for second table stream starts here
import "date"
startRange = date.truncate(t: v.timeRangeStart, unit: 1d)
endRange = date.truncate(t: v.timeRangeStop, unit: 1d)
daysInRange = uint(v: endRange) - uint(v: startRange) // you can omit this line as it does not get used anywhere
t2 = from(bucket: "your-bucket")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "your-measurement")
|> filter(fn: (r) => r["_field"] == "your-field-name")
|> last() // I put this line in so that the query returns just one record instead of many records
|> map(fn: (r) => ({ r with _value: uint(v: endRange) - uint(v: startRange) }))
|> toInt()
|> map(fn: (r) => ({ r with _value: r._value / 86400000000000 })) // converts integer in nanoseconds to days
|> yield(name: "whatever")
// joined table starts here
import "join"
join.inner(
left: t1,
right: t2,
on: (l, r) => l._time == r._time, // not sure about this
as: (l, r) => ({l with label: r._value}), // not sure about this
)
// then include a map function here to do your calculation, i.e. divide t1 result with the result of t2 query to get the average per day
If I understood correctly at @simpixo1
he has a single value that wants to use in a table (output from a query).
In that case just extract an scalar value to a variable:
Extract scalar values in Flux | InfluxDB OSS 2.7 Documentation (influxdata.com),
totalconsupmtion=data
|> findColumn(
fn: (key) => key._field == "delta",
column: "_value",
)
and then use map in your other query dividing values by the variable that contains the scalar value:
|> map(fn: (r) => ({ r with _value: r._value / totalconsupmtion[0] }))
you may need to convert to float both values if you want to preserve decimal places, otherwise dividing ints resluts in ints (it truncates the numbers).
Yes, my understanding is that query #1 gives a single value (it’s the difference between the first and last value of the selected time period) and query #2 gives a single value (it’s the number of days in the selected time period). So yes, extracting the scalar value and then using that in the map function in query #2 should work perfectly (I think something similar was done in another post not too long ago, either here or in the Influx form).
@simpixo1 Can you give it a try?
Hi @fercasjr
I tried your solution in some test data that I had and it almost works, but I get this error. Any suggestions?
That error is because find column has no results is returnina an 0 lenght array.
I think I made a mistake there, key value to find must be a group attribute, try with _measurement and it’s respective value or any tag and its value.
Obtener Outlook para Android
Thanks @fercasjr We’re getting close…
Here is the query. No errors, but the result is not correct. Just to make it simple, I changed the map() function to just display the scalar value. However, instead of 25.5, it gives us 57.56.
import "date"
data = from(bucket: "Bucket1")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "WeatherData")
|> filter(fn: (r) => r["_field"] == "OutdoorTemp")
first = data
|> first()
|> set(key: "_field", value: "delta")
last = data
|> last()
|> set (key: "_field", value: "delta")
union(tables:[first, last])
|> difference()
|> yield()
totalconsumption = data
|> findColumn(
fn: (key) => key._measurement == "WeatherData",
column: "_value",
)
startRange = date.truncate(t: v.timeRangeStart, unit: 1d)
endRange = date.truncate(t: v.timeRangeStop, unit: 1d)
t2=from(bucket: "Bucket1")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "WeatherData")
|> filter(fn: (r) => r["_field"] == "OutdoorTemp")
|> map(fn: (r) => ({ r with _value: uint(v: endRange) - uint(v: startRange) }))
|> toInt()
|> map(fn: (r) => ({ r with _value: r._value / 86400000000000 })) // converts integer in nanoseconds to days
|> limit(n:1, offset: 0)
|> map(fn: (r) => ({ r with avg_per_day: totalconsumption[0] }))
|> yield(name: "whatever")
The delta value is correct (25.5) and the number of days is correct (7).
I think I know what is going on, we are using find column on the original “data”, not in the table that have the difference between last and first, try this:
import "date"
data = from(bucket: "Bucket1")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "WeatherData")
|> filter(fn: (r) => r["_field"] == "OutdoorTemp")
first = data
|> first()
|> set(key: "_field", value: "delta")
last = data
|> last()
|> set (key: "_field", value: "delta")
data2 = data
union(tables:[first, last])
|> difference()
|> yield()
totalconsumption = data2
|> findColumn(
fn: (key) => key._measurement == "WeatherData",
column: "_value",
)
startRange = date.truncate(t: v.timeRangeStart, unit: 1d)
endRange = date.truncate(t: v.timeRangeStop, unit: 1d)
t2=from(bucket: "Bucket1")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "WeatherData")
|> filter(fn: (r) => r["_field"] == "OutdoorTemp")
|> map(fn: (r) => ({ r with _value: uint(v: endRange) - uint(v: startRange) }))
|> toInt()
|> map(fn: (r) => ({ r with _value: r._value / 86400000000000 })) // converts integer in nanoseconds to days
|> limit(n:1, offset: 0)
|> map(fn: (r) => ({ r with avg_per_day: totalconsumption[0] }))
|> yield(name: "whatever")
Obtener Outlook para Android
And a few minor corrections later, and we have success! See below with some additional comments:
import "date"
data = from(bucket: "Bucket1")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "WeatherData")
|> filter(fn: (r) => r["_field"] == "OutdoorTemp")
first = data
|> first()
|> set(key: "_field", value: "delta")
last = data
|> last()
|> set (key: "_field", value: "delta")
data2 = union(tables:[first, last]) // @fercasjr: you had a small typo here, but I knew what you meant
|> difference()
|> yield(name: "table_one")
// extract a scalar value to a variable
totalconsumption = data2
|> findColumn(
fn: (key) => key._measurement == "WeatherData",
column: "_value",
)
// begin second query here to obtain number of whole days in the time selection window
startRange = date.truncate(t: v.timeRangeStart, unit: 1d)
endRange = date.truncate(t: v.timeRangeStop, unit: 1d)
t2=from(bucket: "Bucket1")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "WeatherData")
|> filter(fn: (r) => r["_field"] == "OutdoorTemp")
|> map(fn: (r) => ({ r with _value: uint(v: endRange) - uint(v: startRange) }))
|> toInt()
|> map(fn: (r) => ({ r with _value: r._value / 86400000000000 })) // converts integer in nanoseconds to days
|> limit(n:1, offset: 0)
|> toFloat() // need this as @fercasjr mentioned, otherwise error is "panic: unexpected kind: got "float" expected "int""
|> map(fn: (r) => ({ r with avg_per_day: totalconsumption[0] / r._value }))
|> yield(name: "table_two")
Wow you are great! it works now
hahaha I loved the comment 
In my defense I was using the phone, good catch
1 Like