Flux to InfluxQL?

InfluxDB 2
1

Hi,

i need some help converting a Flux Query into InfluxQL:

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

from(bucket: "solarpower")
  |> range(start: -12mo, stop: now())
  |> filter(fn: (r) => r["_measurement"] == "solar")
  |> filter(fn: (r) => r["_field"] == "todaykwh")
  |> aggregateWindow(every: 24h, fn: last, offset: -1s, createEmpty: false)
  |> truncateTimeColumn(unit: 1m)
  |> aggregateWindow(every: 1mo, fn: sum, createEmpty: false, timeSrc:"_start")

For Grafana Ouput of 12 Bars.

2

@Starfoxfs I believe this query will work for you, but it may not:

SELECT
  SUM(last_todaykwh) AS "monthly_sum"
FROM (
  SELECT
    last(todaykwh) AS "last_todaykwh"
  FROM
    solarpower..solar
  GROUP BY
    time(24h, -1s)
)
GROUP BY
  time(every: 30d)
WHERE
  time >= now() - 365d
  AND time < now()
TZ('Europe/Berlin')

InfluxQL isn’t quite as flexible as Flux and doesn’t support all of the same duration units (mo for example).

1 Like
3

This isnt working correctly, I used it in Grafana and get the Error:

Time column doesnt exists from the last GROUP BY clause.

If i delete the last GROUP BY clause the Query works but makes a sum of alle Last todaykwh in 24h hours thats correct but i need a the Last Todaykwh of all 24Hours for 12 month calculated per Month.

The First SELECT does not connect to a Database its only a calculation of the Second SELECT so the first SELECT cant have a Time column and so the last GROUP BY cant work.

But i dont know how to do this correctly with UNION ?!?

4

InfluxQL doesn’t support UNION. I assume you’re using InfluxDB v2 to run this query and are converting it to InfluxQL. Is there a specific reason you want to use InfluxQL instead of Flux?

5

InfluxDB3 is the reason :grinning_face:

6

There are some Flux queries that can’t be replicated in InfluxQL. I think this query falls in that category. However, you would be able to run a similar query using SQL in InfluxDB 3. The InfluxDB 3 SQL implementation provides more flexibility with subqueries than InfluxQL and it supports UNION. You might need to wait until you move the InfluxDB 3 before you can restructure this query using SQL.

7

Hi, I didn’t try your flux query specifically, but I found AI chatbots to be quite good at the conversion. This is what it spitted out:

```
SELECT SUM(“daily_kwh”)
FROM (
SELECT LAST(“todaykwh”) AS “daily_kwh”
FROM “solar”
WHERE time >= now() - 12mo
GROUP BY time(1d)
)
GROUP BY time(30d)
```

1 Like