How to convert influxql query into flux

Fluxlang
,
1

I am trying to migrate from InlfuxDb 1 to 2. To control some of my processes I have the following query in influxql where the goal is to get the total power consumption to date:

SELECT last(“value”) - first(“value”) FROM “PowerData”.“autogen”.“kWh” WHERE time > ‘2022-01-01T00:00:00Z’ AND time < ‘2022-01-01T23:59:59Z’ AND “entity_id”=‘kWhPower’

So far I got in flux:

data = from(bucket: “PowerData”)
|> range(start: 2022-01-01T00:00:00Z, stop: 2022-01-31T23:59:59Z)
|> filter(fn: (r) => r[“topic”] == “Kaifa”)
|> filter(fn: (r) => r["_field"] == “Kaifa_kWhPower”)

last = data
|> last()
|> set(key: “_field”, value: “Power”)

first = data
|> first()
|> set(key: “_field”, value: “Power”)

union(tables: [last, first])
|> difference()

But how can I select the last and first value in range, subtract them and get a single number as the response? Am I doing this on its best way or there are better ways to do it?

Thanks for helping!

2

@Moacir_Ferreira There are a few different ways to do this.

If your data is only incrementing up, you can use spread() to return the difference between the minimum and maximum values in each table. So if the first value is always the lowest and the last value is always the highest, spread() is the way to go:

from(bucket: "PowerData")
    |> range(start: 2022-01-01T00:00:00Z, stop: 2022-01-31T23:59:59Z)
    |> filter(fn: (r) => r["topic"] == "Kaifa")
    |> filter(fn: (r) => r["_field"] == "Kaifa_kWhPower")
    |> spread()

Something else to note here is that if your kWh value every resets (like some counters do), you can use increase() before spread() to normalize the values after the reset.

If values fluctuate up and down and you only want to return the difference between the first and last values, the query you have, as far as performance and optimization, is great. The only thing I would add is to sort by time after the union to ensure the rows are in the correct order:

data = from(bucket: "PowerData")
    |> range(start: 2022-01-01T00:00:00Z, stop: 2022-01-31T23:59:59Z)
    |> filter(fn: (r) => r["topic"] == "Kaifa")
    |> filter(fn: (r) => r["_field"] == "Kaifa_kWhPower")

last = data |> last() |> set(key: "_field", value: "Power")
first = data |> first() |> set(key: "_field", value: "Power")

union(tables: [last, first]) |> sort(columns: ["_time"], desc: true) |> difference()

Another option would be to create a custom aggregate using reduce(), but it won’t be as efficient as either of the queries above.

3

Many thanks Scott! My case is the first one. It works perfectly!