I’m trying to get the last line I wrote into influxdb in a time range by pivot. The problem is that not all fields are none null.
Currently, I have to query the last record of a none null field and get its _time. Then filter all data by that _time and use pivot to transform data to a row.
Is there a better way to implement that?
Here is the full data. I dumped it by:
import "influxdata/influxdb/schema"
from(bucket: "influxio")
|> range(start: -30d)
|> filter(fn: (r) => r._measurement == "positions")
|> filter(fn: (r) => r.vin == "1636601541-1")
|> schema.fieldsAsCols()
|> sort(columns: ["_time"])
The data:
#group,false,false,true,true,false,true,true,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false
#datatype,string,long,dateTime:RFC3339,dateTime:RFC3339,dateTime:RFC3339,string,string,boolean,boolean,long,double,double,long,double,double,boolean,boolean,boolean,double,double,double,double,double,long,double,long,long
#default,_result,,,,,,,,,,,,,,,,,,,,,,,,,,
,result,table,_start,_stop,_time,_measurement,vin,battery_heater,battery_heater_on,battery_level,driver_temp_setting,est_battery_range_km,fan_status,ideal_battery_range_km,inside_temp,is_climate_on,is_front_defroster_on,is_rear_defroster_on,latitude,longitude,odometer,outside_temp,passenger_temp_setting,power,rated_battery_range_km,speed,usable_battery_level
,,0,2021-10-12T08:55:35.075854Z,2021-11-11T08:55:35.075854Z,2021-11-04T01:50:52.174Z,positions,1636601541-1,false,false,19,21,40.62,5,65.92,19.7,true,false,false,31.202968,121.258191,31470.154494,19.5,21,6,65.92,3,18
,,0,2021-10-12T08:55:35.075854Z,2021-11-11T08:55:35.075854Z,2021-11-04T01:50:53.66Z,positions,1636601541-1,,,19,,,,,,,,,31.202967,121.258188,31470.078182,,,7,,3,
,,0,2021-10-12T08:55:35.075854Z,2021-11-11T08:55:35.075854Z,2021-11-04T01:50:53.91Z,positions,1636601541-1,,,19,,,,,,,,,31.202967,121.258195,31470.078182,,,6,,3,
,,0,2021-10-12T08:55:35.075854Z,2021-11-11T08:55:35.075854Z,2021-11-04T01:50:55.159Z,positions,1636601541-1,,,19,,,,,,,,,31.20298,121.258221,31470.078182,,,7,,3,
,,0,2021-10-12T08:55:35.075854Z,2021-11-11T08:55:35.075854Z,2021-11-04T01:50:56.91Z,positions,1636601541-1,,,19,,,,,,,,,31.20299,121.258226,31470.239117,,,6,,3,
,,0,2021-10-12T08:55:35.075854Z,2021-11-11T08:55:35.075854Z,2021-11-04T01:50:57.41Z,positions,1636601541-1,,,19,,,,,,,,,31.20299,121.258226,31470.239117,,,8,,3,
,,0,2021-10-12T08:55:35.075854Z,2021-11-11T08:55:35.075854Z,2021-11-04T01:51:00.909Z,positions,1636601541-1,,,19,,,,,,,,,31.203013,121.258262,31470.239117,,,9,,6,
,,0,2021-10-12T08:55:35.075854Z,2021-11-11T08:55:35.075854Z,2021-11-04T01:51:01.16Z,positions,1636601541-1,,,19,,,,,,,,,31.203013,121.258262,31470.239117,,,11,,6,
,,0,2021-10-12T08:55:35.075854Z,2021-11-11T08:55:35.075854Z,2021-11-04T01:51:01.41Z,positions,1636601541-1,,,19,,,,,,,,,31.203013,121.258262,31470.239117,,,9,,6,
,,0,2021-10-12T08:55:35.075854Z,2021-11-11T08:55:35.075854Z,2021-11-04T01:51:03.66Z,positions,1636601541-1,,,19,,,,,,,,,31.203022,121.258298,31470.239117,,,5,,3,
,,0,2021-10-12T08:55:35.075854Z,2021-11-11T08:55:35.075854Z,2021-11-04T01:51:05.409Z,positions,1636601541-1,,,19,,,,,,,,,31.203032,121.258315,31470.239117,,,5,,2,
,,0,2021-10-12T08:55:35.075854Z,2021-11-11T08:55:35.075854Z,2021-11-04T01:51:08.91Z,positions,1636601541-1,,,19,,,,,,,,,31.203043,121.258338,31470.239117,,,7,,3,
,,0,2021-10-12T08:55:35.075854Z,2021-11-11T08:55:35.075854Z,2021-11-04T01:51:10.161Z,positions,1636601541-1,,,19,,,,,,,,,31.203049,121.258347,31470.239117,,,5,,6,
,,0,2021-10-12T08:55:35.075854Z,2021-11-11T08:55:35.075854Z,2021-11-04T01:51:12.91Z,positions,1636601541-1,,,19,,,,,,,,,31.203061,121.258405,31470.239117,,,3,,6,
,,0,2021-10-12T08:55:35.075854Z,2021-11-11T08:55:35.075854Z,2021-11-04T01:51:17.909Z,positions,1636601541-1,,,19,,,,,,,,,31.203025,121.258447,31470.239117,,,3,,0,
,,0,2021-10-12T08:55:35.075854Z,2021-11-11T08:55:35.075854Z,2021-11-04T01:51:21.66Z,positions,1636601541-1,,,19,,,,,,,,,31.203041,121.258442,31470.239117,,,15,,16,
,,0,2021-10-12T08:55:35.075854Z,2021-11-11T08:55:35.075854Z,2021-11-04T01:51:23.909Z,positions,1636601541-1,,,19,,,,,,,,,31.203145,121.258581,31470.239117,,,-7,,27,
,,0,2021-10-12T08:55:35.075854Z,2021-11-11T08:55:35.075854Z,2021-11-04T01:51:24.41Z,positions,1636601541-1,,,19,,,,,,,,,31.203145,121.258581,31470.239117,,,-17,,24,
,,0,2021-10-12T08:55:35.075854Z,2021-11-11T08:55:35.075854Z,2021-11-04T01:51:26.41Z,positions,1636601541-1,,,19,,,,,,,,,31.20321,121.258707,31470.239117,,,0,,14,
,,0,2021-10-12T08:55:35.075854Z,2021-11-11T08:55:35.075854Z,2021-11-04T01:51:26.91Z,positions,1636601541-1,,,19,,,,,,,,,31.203217,121.258739,31470.239117,,,3,,13,
,,0,2021-10-12T08:55:35.075854Z,2021-11-11T08:55:35.075854Z,2021-11-04T01:51:41.438Z,positions,1636601541-1,false,false,19,21,39.75,5,65.11,20.3,true,false,false,31.203161,121.25914,31470.280495,19.5,21,6,65.11,2,18
,,0,2021-10-12T08:55:35.075854Z,2021-11-11T08:55:35.075854Z,2021-11-04T01:51:41.66Z,positions,1636601541-1,,,19,,,,,,,,,31.203161,121.25914,31470.239117,,,7,,2,
,,0,2021-10-12T08:55:35.075854Z,2021-11-11T08:55:35.075854Z,2021-11-04T01:51:48.41Z,positions,1636601541-1,,,19,,,,,,,,,31.203231,121.259273,31470.239117,,,14,,11,
I’m currently using a strange way to achieve this feature.
import "influxdata/influxdb/schema"
from(bucket: "influxio")
|> range(start: -30d)
|> filter(fn: (r) => r._measurement == "positions")
|> filter(fn: (r) => r.vin == "1636601541-1")
|> last()
|> schema.fieldsAsCols()
|> filter(fn: (r) => r.odometer != 0) // To drop other lines generated by null fields
|> last(column: "_time")
@magine The last() function selects the last non-null record from an input table. If this column is null in the last record, last() returns the previous record with a non-null value. Default is "_value". So in your case, you can use last(column: "odometer") after the filter if you want the non-null value of the odometer.
Other option is to sort by _time and use limit() function to get the last record.
|> sort(columns: ["_time"], desc: false) |> limit(n:1)
