Union(): Type error record is missing label _time

Fluxlang
, , ,
1

I am unable to combine two tables using union(). The data is from my energy smartmeter already aggregated to daily summaries. Now I want to aggreagate the daily data further into a monthly summary using a flux task.

Here is the query I have written together with some sample data embedded as csv:

import "csv"
daily = csv.from(csv: "
#group,false,false,true,true,false,false,true,true
#datatype,string,long,dateTime:RFC3339,dateTime:RFC3339,dateTime:RFC3339,double,string,string
#default,_result,,,,,,,
,result,table,_start,_stop,_time,_value,_field,_measurement
,,0,2020-12-20T00:00:00Z,2021-03-16T18:41:22.528676486Z,2021-02-19T00:00:00Z,2.88089286293994,bill,daily
,,0,2020-12-20T00:00:00Z,2021-03-16T18:41:22.528676486Z,2021-03-15T00:00:00Z,2.99105048753981,bill,daily
,,0,2020-12-20T00:00:00Z,2021-03-16T18:41:22.528676486Z,2021-03-16T00:00:00Z,2.85227309833964,bill,daily
,,1,2020-12-20T00:00:00Z,2021-03-16T18:41:22.528676486Z,2021-02-19T00:00:00Z,9.88837700000022,energy,daily
,,1,2020-12-20T00:00:00Z,2021-03-16T18:41:22.528676486Z,2021-03-15T00:00:00Z,10.3424480000003,energy,daily
,,1,2020-12-20T00:00:00Z,2021-03-16T18:41:22.528676486Z,2021-03-16T00:00:00Z,9.77040599999964,energy,daily
,,2,2020-12-20T00:00:00Z,2021-03-16T18:41:22.528676486Z,2021-02-19T00:00:00Z,0.2426,price,daily
,,2,2020-12-20T00:00:00Z,2021-03-16T18:41:22.528676486Z,2021-03-15T00:00:00Z,0.2426,price,daily
,,2,2020-12-20T00:00:00Z,2021-03-16T18:41:22.528676486Z,2021-03-16T00:00:00Z,0.2426,price,daily
,,3,2020-12-20T00:00:00Z,2021-03-16T18:41:22.528676486Z,2021-02-19T00:00:00Z,14.66,rate,daily
,,3,2020-12-20T00:00:00Z,2021-03-16T18:41:22.528676486Z,2021-03-15T00:00:00Z,14.66,rate,daily
,,3,2020-12-20T00:00:00Z,2021-03-16T18:41:22.528676486Z,2021-03-16T00:00:00Z,14.66,rate,daily
,,4,2020-12-20T00:00:00Z,2021-03-16T18:41:22.528676486Z,2021-02-19T00:00:00Z,4448.801833,total,daily
,,4,2020-12-20T00:00:00Z,2021-03-16T18:41:22.528676486Z,2021-03-15T00:00:00Z,4701.490756,total,daily
,,4,2020-12-20T00:00:00Z,2021-03-16T18:41:22.528676486Z,2021-03-16T00:00:00Z,4711.2612,total,daily
")

data = daily
  |> range(start: -1mo)
  |> filter(fn: (r) => (r._measurement == "daily"))
//  |> yield(name: "data")

month_end = data
  |> filter(fn: (r) =>
    (r._field == "rate" or r._field == "price" or r._field == "total"))
// last() outputs the first point if not given the column: "_time" argument. Bug???
  |> last(column: "_time")
  |> map(fn: (r) => ({r with _time: now()}))
//  |> yield(name: "month_end")

energy = data
  |> filter(fn: (r) => (r._field == "energy"))
  |> sum()
// sum drops the time column. Ideally I would like to use the timestamp of the last day,
// the following map adds the time column back and also orders the columns.
  |> map(fn: (r) => ({r with _time: now()}))
  |> set(key: "_measurement", value: "monthly")
//  |> yield(name: "energy")

res = union(tables: [energy, month_end])
	|> group(columns: ["_field"], mode: "by")
  |> set(key: "_measurement", value: "monthly")
  |> yield(name: "res")

bill = res
  |> pivot(
       rowKey: ["_measurement", "_start", "_stop", "_time"], 
       columnKey: ["_field"], 
       valueColumn: "_value"
     )
  |> set(key: "_field", value: "bill")
  |> map(fn: (r) => ({
    _measurement: r._measurement,
    _field: r._field,
    _start: r._start,
    _stop: r._stop,
    _time: r._time,
    _value: r.energy * r.price + r.rate}))
// set group of _field column to true
  |> group(columns: ["_field"], mode: "by")
  |> yield(name: "bill")

// this is where it fails
union(tables: [res, bill])

res:

#group,false,false,true,false,false,false,false,false
#datatype,string,long,string,string,dateTime:RFC3339,dateTime:RFC3339,dateTime:RFC3339,double
#default,res,,,,,,,
,result,table,_field,_measurement,_start,_stop,_time,_value
,,0,energy,monthly,2021-02-17T06:34:11.161530825Z,2021-03-16T18:41:22.528676486Z,2021-03-19T17:04:11.161530825Z,30.00123100000016
,,1,price,monthly,2021-02-17T06:34:11.161530825Z,2021-03-16T18:41:22.528676486Z,2021-03-19T17:04:11.161530825Z,0.2426
,,2,rate,monthly,2021-02-17T06:34:11.161530825Z,2021-03-16T18:41:22.528676486Z,2021-03-19T17:04:11.161530825Z,14.66
,,3,total,monthly,2021-02-17T06:34:11.161530825Z,2021-03-16T18:41:22.528676486Z,2021-03-19T17:04:11.161530825Z,4711.2612

bill:

#group,false,false,true,false,false,false,false,false
#datatype,string,long,string,string,dateTime:RFC3339,dateTime:RFC3339,dateTime:RFC3339,double
#default,bill,,,,,,,
,result,table,_field,_measurement,_start,_stop,_time,_value
,,0,bill,monthly,2021-02-17T06:34:11.161530825Z,2021-03-16T18:41:22.528676486Z,2021-03-19T17:04:11.161530825Z,21.93829864060004

I should be able to merge these tables into one as all columns have the same column order, group key and data type. I get the following error:

Error: Type error @65:21-65:25: record is missing label _time.
See 'influx query -h' for help

Clearly both tables have a “_time” column.
Why is the call to union() failing?

2

For others that run into this page - I had the same issue and found out how to fix it. Why this isn’t working I don’t know, I have to assume there is something special about the column name _time.

Anyway, the fix is to name your time column something else, and then rename it after the array.from call.

Like so:

emptycommand = array.from(rows: [{
        t:  time(v: 0),        
        cancel:debug.null(type: "string"),
        commandType: debug.null(type: "string"),
        commandName: debug.null(type: "string"),
        deviceId:debug.null(type: "string")
    }])
    |> rename(columns: { t: "_time"})

commands2 = union(tables: [emptycommand, commands])

Hope this helps!

1 Like