I’m trying to collect monthly settings for 4 counters called cT1, cT2, fT1, fT2 and calculate the difference between the end-of-month and start-of-month value and use these to compute the result “yld”. The problem is that the query twice offsets the timestamps by 1 month: once as a result of aggregateWindow() and once as a result of difference()
I’m running the following query:
import "timezone"
option location = timezone.location(name: "Europe/Amsterdam")
data24h = from(bucket: "e-counters")
|> range(start: 2022-01-01)
|> filter(fn: (r) => r["_measurement"] == "electricity")
|> filter(fn: (r) => r["agg_type"] == "last24h")
pre_process = (tables=<-, window) => tables
|> filter(fn: (r) => r["_field"] =~ /[cf]T[1-2]/ ) // cT1, cT2, fT1, fT2
|> aggregateWindow(every: window, fn: first, createEmpty: false)
|> drop(columns: ["_start", "_stop", "agg_type", "_measurement"])
data_1mo = data24h
|> pre_process(window: 1mo)
// |> yield(name: "data_1mo")
yld = data_1mo
|> group()
|> sort(columns: ["_field", "_time"], desc: false)
|> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
// |> duplicate(column: "_time", as: "original_time")
|> yield(name: "intermediate_result")
|> difference(columns: ["fT1", "fT2", "cT1", "cT2"])
|> map(fn: (r) => ({ r with yld: (r.fT1 + r.fT2) - (r.cT1 + r.cT2) }))
// |> keep(columns: ["yld", "_time"])
|> yield(name: "yld")
with the following result:
table
intermediate_result
_time
no group
dateTime:RFC3339
cT1
no group
double
cT2
no group
double
fT1
no group
double
fT2
no group
double
0 2022-01-31T23:00:00.000Z 11513.767 6662.066 12434.695 28019.407
0 2022-02-28T23:00:00.000Z 11672.555 6789.526 12469.196 28091.143
0 2022-03-31T22:00:00.000Z 11796.889 6873.731 12610.354 28312.171
0 2022-04-14T10:10:02.217Z 11935.733 6953.828 12853.836 29036.859
table
yld
_time
no group
dateTime:RFC3339
cT1
no group
double
cT2
no group
double
fT1
no group
double
fT2
no group
double
yld
no group
double
0 2022-02-28T23:00:00.000Z 158.78800000000047 127.46000000000004 34.501000000000204 71.73600000000079 -180.0109999999995
0 2022-03-31T22:00:00.000Z 124.33399999999892 84.20499999999993 141.15799999999945 221.02799999999843 153.64699999999903
0 2022-04-14T10:10:02.217Z 138.84400000000096 80.09700000000066 243.48199999999997 724.6880000000019 749.2290000000003
As an example: the correct value for the difference in counter fT2 for the month of March is 749.23. I expect the timestamp for this to be 2022-03-31T22:00:00.000Z but it’s returning 2022-04-14T10:10:02.217Z which is the current time. I would also expect this timestamp to be used for the partial month of April, but there’s no result at all for April.
How can this query be changed to deliver the correct values for FEB, MAR & APR with either end-of-month or start-of-month (preferred) timestamps?