Split query into three groups

InfluxDB 2
1

Hello. I have a query in influxdb 2 that gets the imported electrical power in my house. This is the query:

from(bucket: "energymeter")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "IMPORTADA")
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false, timeSrc: "_start")
|> integral(unit: 1h, column: "_value")
|> keep(columns: ["_time","_field","_value","_measurement"])
|> map(fn: (r) => ({r with _value: r._value / 1000.0}))

My desire is to divide the day into three periods, P1 (10:00 a.m. - 10:00 p.m.), P2 (10:00 p.m. - 12:00 a.m.) and P3 (12:00 a.m. - 10:00 a.m.), and obtain the energy consumed in each period. Can you help me?

2

Hello @s118,
Welcome back.
Yes absolutely. I apologize for the delay.
You can use the following function:

Let me know if you need more help.

3

You’ll want to use the followign logic:

data = from(bucket: "energymeter")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "IMPORTADA")
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false, timeSrc: "_start")
|> integral(unit: 1h, column: "_value")
|> keep(columns: ["_time","_field","_value","_measurement"])
|> map(fn: (r) => ({r with _value: r._value / 1000.0}))

firstSelection = data 
|> hourSelection(start: 10, stop: 22)
|> yield(name: "first selection")

secondSelection = data 
|> hourSelection(start: 22, stop: 24)
|> yield(name: "second selection")

thirdSelection = data 
|> hourSelection(start: 24, stop: 10)
|> yield(name: "third selection")
4

Thank you.
What would it be like if a period had two time slots, for example P1: 10-15 and 18-22?

5

@s118 You’d have to union to streams together:

// ...

fourthSelection = union(tables: [
    data |> hourSelection(start: 10, stop: 15),
    data |> hourSelection(start: 18, stop: 22),
])
6

If I put the code as indicated, it gives me error: “invalid time column” (referring to the rows that contain hourSelection())

7

@s118 integral() is an aggregate function, so it will drop the time column. You need to duplicate the _start or _stop column as a new _time column after you run integral().

data =
    from(bucket: "energymeter")
        |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
        |> filter(fn: (r) => r["_measurement"] == "IMPORTADA")
        |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false, timeSrc: "_start")
        |> integral(unit: 1h, column: "_value")
        |> duplicate(column: "_start", as: "_time")
        |> keep(columns: ["_time","_field","_value","_measurement"])
        |> map(fn: (r) => ({r with _value: r._value / 1000.0}))

firstSelection =
    data 
        |> hourSelection(start: 10, stop: 22)
        |> yield(name: "first selection")

secondSelection =
    data 
        |> hourSelection(start: 22, stop: 24)
        |> yield(name: "second selection")

thirdSelection =
    data 
        |> hourSelection(start: 24, stop: 10)
        |> yield(name: "third selection")

fourthSelection =
    union(tables: [
        data |> hourSelection(start: 10, stop: 15),
        data |> hourSelection(start: 18, stop: 22),
    ])
    |> yield(name: "fourth selection")
8

Thanks for everything