I have a measurement series (generation for multiple solar inverters) and I want so select from a different data series at the time when the solar generation completed (yesterday) and started (today) - how much electricty was used overnight.
Very long query, only a last few rows matter:
import "experimental"
// Get yesterdays solar data
garagedataYesterday = from(bucket: "nodered")
|> range(start: experimental.subDuration(d: 24h, from: today()), stop: today())
|> filter(fn: (r) => r["_measurement"] == "solar")
|> filter(fn: (r) => r["inverter"] == "garage")
|> filter(fn: (r) => r["_field"] == "outputpower")
|> aggregateWindow(every: 5m, fn: mean, createEmpty: false)
smalldataYesterday = from(bucket: "nodered")
|> range(start: experimental.subDuration(d: 24h, from: today()), stop: today())
|> filter(fn: (r) => r["_measurement"] == "solar")
|> filter(fn: (r) => r["inverter"] == "small")
|> filter(fn: (r) => r["_field"] == "outputpower")
|> aggregateWindow(every: 5m, fn: mean, createEmpty: false)
olddataYesterday = from(bucket: "nodered")
|> range(start: experimental.subDuration(d: 24h, from: today()), stop: today())
|> filter(fn: (r) => r["_measurement"] == "solar")
|> filter(fn: (r) => r["inverter"] == "old")
|> filter(fn: (r) => r["_field"] == "outputpower")
|> aggregateWindow(every: 5m, fn: mean, createEmpty: false)
yesterdaySolar = union(tables: [garagedataYesterday,smalldataYesterday,olddataYesterday])
|> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
|> group(columns: ["_time", "_measurement"])
|> pivot(rowKey:["_time"], columnKey: ["inverter"], valueColumn: "outputpower")
|> group()
|> map(fn: (r) => ({r with garage: if exists r.garage then r.garage else 0.0}))
|> map(fn: (r) => ({r with small: if exists r.small then r.small else 0.0}))
|> map(fn: (r) => ({r with old: if exists r.old then r.old else 0.0}))
|> map(fn: (r) => ({r with _value: r.garage+r.old+r.small}))
|> drop(columns: ["garage","old","small"])
|> last()
garagedataToday = from(bucket: "nodered")
|> range(start: today(), stop: experimental.addDuration( d: 24h, to: today()))
|> filter(fn: (r) => r["_measurement"] == "solar")
|> filter(fn: (r) => r["inverter"] == "garage")
|> filter(fn: (r) => r["_field"] == "outputpower")
|> aggregateWindow(every: 5m, fn: mean, createEmpty: false)
smalldataToday = from(bucket: "nodered")
|> range(start: today(), stop: experimental.addDuration( d: 24h, to: today()))
|> filter(fn: (r) => r["_measurement"] == "solar")
|> filter(fn: (r) => r["inverter"] == "small")
|> filter(fn: (r) => r["_field"] == "outputpower")
|> aggregateWindow(every: 5m, fn: mean, createEmpty: false)
olddataToday = from(bucket: "nodered")
|> range(start: today(), stop: experimental.addDuration( d: 24h, to: today()))
|> filter(fn: (r) => r["_measurement"] == "solar")
|> filter(fn: (r) => r["inverter"] == "old")
|> filter(fn: (r) => r["_field"] == "outputpower")
|> aggregateWindow(every: 5m, fn: mean, createEmpty: false)
todaySolar = union(tables: [garagedataToday,smalldataToday,olddataToday])
|> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
|> group(columns: ["_time", "_measurement"])
|> pivot(rowKey:["_time"], columnKey: ["inverter"], valueColumn: "outputpower")
|> group()
|> map(fn: (r) => ({r with garage: if exists r.garage then r.garage else 0.0}))
|> map(fn: (r) => ({r with small: if exists r.small then r.small else 0.0}))
|> map(fn: (r) => ({r with old: if exists r.old then r.old else 0.0}))
|> map(fn: (r) => ({r with _value: r.garage+r.old+r.small}))
|> drop(columns: ["garage","old","small"])
|> first()
timestamps = union(tables: [ yesterdaySolar, todaySolar])
|> findColumn(fn: (key) => true, column: "_time")
from(bucket: "nodered")
|> range(start: timestamps[0], stop: timestamps[1])
|> filter(fn: (r) => r["_measurement"] == "sensors")
|> filter(fn: (r) => r["device"] == "house_phase0" or r["device"] == "house_phase1" or r["device"] == "house_phase2")
|> filter(fn: (r) => r["_field"] == "total")
The error I am getting is the following: error in building plan while starting program: cannot query an empty range
I assume this is the range that I specify here: range(start: timestamps[0], stop: timestamps[1])
But if I remove some of the last few rows and execute the query again, it seems to select the start and end date as expected:
My understanding is that findColumn(fn: (key) => true, column: "_time") should reduce the table into an array of _time values, and I should be able to reference to them in the next range statement as timestamps[0] and timestamps[1].
What did I do wrong?
