How can I retain the time column in this query?

Fluxlang
1

I have the following query which produces the correct result:

from(bucket: "energy")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "pv")
  |> filter(fn: (r) => r["serial"] == "3002969" or r["serial"] == "2130216")
  |> filter(fn: (r) => r["_field"] =~ /Iac[1-3]/)
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> mean()
  |> group(columns: ["_field"])
  |> sum()
  |> yield(name: "Iac_total")

The problem is that for understandable reasons, the _time column is no longer present. Is there any way to retain the _time column while also retaining the query’s functionality? Bing Chat has failed so far in solving this problem.

Here’s a sample of the output:

0	Iac1	1.2044888888888892
1	Iac2	0.5618222222222223
2	Iac3	0.5603333333333335
2

yes:

Create custom aggregate functions | InfluxDB Cloud (TSM) Documentation (influxdata.com)

another more complicated is to create 2 queries one to get the average, then other to get the “last” or “first” value and then combine the two and keep only one single point with the value of first query and time of the second query.

a third option could be to do another aggregateWindow on which every equal to the total duration in between _start _stop. that way the result will be only one data point

also for the sum() you could replace that whith cummulativesum and last:

  |> cumulativeSum(columns: ["_value"])  
  |> last()
3

Hello @fercasjr
Just wanted to thank you for being so active here. I really appreciate it!

4

@fercasjr Thank you for responding. After a lot of experimentation I arrived at the following solution:

data = from(bucket: "energy")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "pv")
  |> filter(fn: (r) => r["_field"] =~ /Iac[1-3]/)
  |> aggregateWindow(every: 60s, fn: mean, createEmpty: false)
  |> keep(columns: ["_time", "_field", "_value", "serial", "_stop"])
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> yield(name: "data")

data0 = data
  |> filter(fn: (r) => r["serial"] == "3002969")
  |> yield(name: "data0")
  
data1 = data
  |> filter(fn: (r) => r["serial"] == "2130216")
  |> yield(name: "data1")

joined = join(tables: {t1: data0, t2: data1}, on: ["_time"])
  |> yield(name: "joined")

result = joined
  |> map(fn: (r) => ({ r with IL1: -(r.Iac1_t1 + r.Iac1_t2) }))
  |> map(fn: (r) => ({ r with IL2: -(r.Iac2_t1 + r.Iac2_t2) }))
  |> map(fn: (r) => ({ r with IL3: -(r.Iac3_t1 + r.Iac3_t2) }))
  |> keep(columns: ["_time", "IL1", "IL2", "IL3"])
  |> yield(name: "Iac_per_phase")

This yields the correct result and avoids using a reduce function. There is a bit of repetitive code that I don’t know how to replace with better code; if you see any opportunities for simplification, that would be very welcome.