Migrate query from InfluxQL to Flux for power consuption

Fluxlang
1

Hi all, i need migrate my query from influxql to flux. I need to generate histogram to calculate Power consuption grouped by day.

Actually (i don’t know if is the best way) i’m using this query in influxql language.

SELECT mean("mean")*24 FROM (SELECT mean("pdc_pt_in_w") FROM "KWh" WHERE ("pdc_pt_prod_acs" = 0 AND "pdc_pt_defrosting" = 0) AND $timeFilter  GROUP BY time(10s) fill(0) tz('Europe/Rome') ) GROUP BY time(1d) fill(0) tz('Europe/Rome')

who helps me to generate the exact same query in Flux language format?

Thanks to all

2

Hello @TheMiloNet,
I had to make some assumptions primarily that the following are all fields:
pdc_pt_defrosting, pdc_pt_prod_acs, and pdc_pt_in_w
The translation of:

SELECT mean("mean")*24 FROM (SELECT mean("pdc_pt_in_w") FROM "KWh" WHERE ("pdc_pt_prod_acs" = 0 AND "pdc_pt_defrosting" = 0) AND $timeFilter  GROUP BY time(10s) fill(0) tz('Europe/Rome') ) GROUP BY time(1d) fill(0) tz('Europe/Rome')

Would look like:

import "timezone"

option location = timezone.location(name: "Europe/Rome")

from(bucket: "your buckey")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "KWh")
  |> schema.fieldAsCol()
  |> filter(fn: (r) => r["pdc_pt_prod_acs"] == 0 and r["pdc_pt_defrosting"] ==  0)
  |> filter(fn: (r) => r["_field"] == "pdc_pt_in_w")
  |> aggregateWindow(every: v.10s, fn: mean, createEmpty: false)
  |> aggregateWindow(every: v.1d, fn: mean, createEmpty: false)
  |> map(fn: (r) => ({ _value: r._value * 24}))

I think that should do it.
The following resources might be helpful to you:

Time Zones in Flux | InfluxData.

3

Hi @Anaisdg I thank you infinitely for the answer. A question. the schema function. Does it also apply to paid cloud 2.x instances?

I have this error:

error @12:29-12:32: invalid binary operator <INVALID_OP>

error @12:32-12:33: undefined identifier d

error @12:29-12:33: invalid binary operator <INVALID_OP>

error @11:29-11:33: invalid binary operator <INVALID_OP>

error @11:33-11:34: undefined identifier s

error @11:29-11:34: invalid binary operator <INVALID_OP>

error @8:6-8:12: undefined identifier schema

Thanks so much

4

Hi @Anaisdg … i have some updates…i’m working on cloud payd infrastructure…On cloud, bucket and _measurement have different names… no problem

I need to add import schema library and remove “v.” from times aggregation…
Whit this query i have results.

import "influxdata/influxdb/schema"
import "timezone"

option location = timezone.location(name: "Europe/Rome")

from(bucket: "kitalog")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "KitaPT")
  |> schema.fieldsAsCols()
  |> filter(fn: (r) => r["pdc_pt_prod_acs"] == 1 and r["pdc_pt_defrosting"] ==  0)
  |> aggregateWindow(every: 10s, fn: mean, createEmpty: false)
  |> aggregateWindow(every: 1d, fn: mean, createEmpty: false)

image
image1826×325 25.5 KB

adding filter function none results

import "influxdata/influxdb/schema"
import "timezone"

option location = timezone.location(name: "Europe/Rome")

from(bucket: "kitalog")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "KitaPT")
  |> schema.fieldsAsCols()
  |> filter(fn: (r) => r["pdc_pt_prod_acs"] == 1 and r["pdc_pt_defrosting"] ==  0)
  |> filter(fn: (r) => r["_field"] == "pdc_pt_in_w")

How can solve?