Help for InfluxQL to Flux

InfluxDB 2
1

Hello community.
Is there anyone here who can translate 2 lines from influxQL to Flux for me? Unfortunately I can’t find any comparable examples. It’s about a query of an influxDB 2 database.

SELECT Total_Ticks FROM Weather_station ORDER BY DESC LIMIT 1

…and the second query:

SELECT difference(max(“Total_Ticks”)) FROM “Weather_station” WHERE time>now()-2d and time <= now() group by time(1d) tz(‘Europe/Berlin’)

I already know how to set the filters so that I get the right data:

from(bucket: “bucket”)
|> filter(fn: (r) => r[“_measurement”] == “Weather_station”)
|> filter(fn: (r) => r[“_field”] == “Total_Ticks”)
…???

But then I just don’t know what to do ;-(
For someone who is fit in Flux, this is certainly easy. But for me it is an insurmountable obstacle.
Can someone help please?

Beste regards

2

Welcome @weissglut

Maybe this?

from(bucket: “bucket”)
|> range(start: -2h) // Query a time range relative to now
|> filter(fn: (r) => r["_measurement"] == "Weather_station")
|> filter(fn: (r) => r["_field"] == "Total_Ticks")
|> max() // returns the row with the maximum value in a specified column from each input table
|> difference() // returns the difference between subsequent values
|> limit(n: 1) //  returns the first n rows 
|> yield(name: "your-query")

Since you are getting 1 row with the above query, I am not sure you need to worry about putting in an aggregateWindow(every: 1d, fn: mean) function.

3

Hello and thank you for the quick reply. I am not sure if I understood everything correctly. The first query in influxQL is:
SELECT Total_Ticks FROM Weather_station ORDER BY DESC LIMIT 1.

Translated into Flux:

from(bucket: "bucket")
    |> range(start: -2h) // Query a time range relative to now
    |> filter(fn: (r) => r["_measurement"] == "weather_station_Igersheim")
    |> filter(fn: (r) => r["_field"] == "Total_Ticks") 
    |> max() // returns the row with the maximum value in a specified column from each input table

Did I understand that correctly?

And for the second query:
SELECT difference(max("Total_Ticks")) FROM "Weather_station" WHERE time>now()-2d and time <= now() group by time(1d) tz('Europe/Berlin')

Is the correct “translation”

from(bucket: "bucket")
  |> range(start: -2h) // Query a time range relative to now
  |> filter(fn: (r) => r["_measurement"] == "Weather_station")
  |> filter(fn: (r) => r["_field"] == "Total_Ticks")
  |> difference() // returns the difference between subsequent values
  |> limit(n: 1) // returns the first n rows 
  |> yield(name: "your-query")

Is that correct?

What does “|> yield(name: “your-query”)” mean?
And how can I find out if “aggregateWindow(every: 1d, fn: mean)” is necessary?

Thanks a lot for any feedback!

4

Sorry, I was going too fast yesterday and did not realize you had 2 queries. The yield statement is needed to put the results into a table that can be viewed in Influx Data Explorer.

I’ll try to post back in the next 24 hours or when time allows.

1 Like
5

should be something like this:

from(bucket: "your-bucket-name")
    |> filter(fn: (r) => r["_measurement"] == "Weather_station")
    |> filter(fn: (r) => r["_field"] == "Total_Ticks")
    |> sort(columns: ["_time"], desc: true)
    |> limit(n: 1)
    |> yield(name: "query1")

should be something like this:

import "timezone"

from(bucket: "your-bucket-name")
    |> range(start: -2d, stop: now())
    |> filter(fn: (r) => r["_measurement"] == "Weather_station")
    |> filter(fn: (r) => r["_field"] == "Total_Ticks")
    |> aggregateWindow(every: 1d, fn: max, createEmpty: false)
    |> difference(nonNegative: false, columns: ["_value"])
    |> map(fn: (r) => ({ r with _time: timezone.shift(t: r._time, location: "Europe/Berlin")}))
    |> yield(name: "query1")

I am a bit sketchy about the whole timezone thing. I always store data in InfluxDB in UTC and then use the local time zone when I am querying it (in InfluxDB or Grafana).

Try running the above in Influx Data Explorer with Raw Data toggle enabled and see if they produce the correct results.

6

Hello grant1.

Thank you very much for your support! With your first solution for
SELECT Total_Ticks FROM Weather_station ORDER BY DESC LIMIT 1

I get the following error message in Influx Data Explorer as well as in the NodeRed application:

error in building plan while starting program: cannot submit unbounded read to "bucket"; try bounding 'from' with a call to 'range'

A time range still needs to be defined here, right?

There also seems to be a problem with the second query:
error @9:40-9:48: record is missing label shift

Do you have any idea what is going wrong here?

Many greetings
Weissglut

7

Hi @weissglut

In the first query, just insert a range function like
|> range(start: -2d, stop: now())
and you should be good there.

In the second query, like I said, my skills are a bit sketchy on the whole timezone thing. Does this work (basically remove the timezone stuff)?

from(bucket: "your-bucket-name")
    |> range(start: -2d, stop: now())
    |> filter(fn: (r) => r["_measurement"] == "Weather_station")
    |> filter(fn: (r) => r["_field"] == "Total_Ticks")
    |> aggregateWindow(every: 1d, fn: max, createEmpty: false)
    |> difference(nonNegative: false, columns: ["_value"])
    |> yield(name: "query1")

EDIT: If the above works, try putting this in at the very beginning before the from(bucket) statement, and see if it still works with the correct time:

import "timezone"
// Set location to be Europe/Berlin
option location = timezone.location(name: "Europe/Berlin")
8

Hello grant1

Now it works without error message! Perfect.
Thanks a lot for your support. Without this platform and your help, I probably wouldn’t have been able to do this.

Many greetings