Align measurements to nearest light reading

HI all!
I am trying to query atmospheric values from a bucket of sensor data, but need to know when the lights are on or off for each reading.

My schema writes temperature, pressure, light, etc as separate measurements. I have been able to pivot both temperature and light times when the light measurement exists, but most of the temperature readings show up alone as the light sensor only reports every 10 or 15 minutes.

My goal is to query all temperature readings, and somehow match the last light reading (before the temperature reading) to each temperature reading so I know if the lights were on or off at that moment.

Is pivot the right function? I assumed that pivoting on measurement would work, but so far it’s a no go. I also found that aggregateWindow doesn’t work as it wants to align both on a single boundary, which makes light unavailable for the next temperature reading.

Anyway, hopefully this is simpler than I’m making it. Any thoughts are much appreciated. Thanks!

@rstuff When you pivot the data, rows where no light value was found should have a null light column. You can use fill(column: "light", usePrevious:true) grab the last non-null value in that column and use it to fill the value.

For example, when you pivot your data, I’m guessing it looks something like this:

_time light temp pressure
2021-01-01T00:00:00Z on 70.1 30.1
2021-01-01T00:05:00Z 70.1 30.1
2021-01-01T00:10:00Z 70.5 30.5
2021-01-01T00:15:00Z off 70.4 30.4
2021-01-01T00:20:00Z 70.1 30.1
2021-01-01T00:25:00Z 70.1 30.1
2021-01-01T00:30:00Z off 70.5 30.5
2021-01-01T00:35:00Z 70.4 30.4

Note: You may need to sort your data by time after pivot(). It doesn’t always guarantee sort order.

You can use fill(column: "light", usePrevious: true) to get the following output:

_time light temp pressure
2021-01-01T00:00:00Z on 70.1 30.1
2021-01-01T00:05:00Z on 70.1 30.1
2021-01-01T00:10:00Z on 70.5 30.5
2021-01-01T00:15:00Z off 70.4 30.4
2021-01-01T00:20:00Z off 70.1 30.1
2021-01-01T00:25:00Z off 70.1 30.1
2021-01-01T00:30:00Z off 70.5 30.5
2021-01-01T00:35:00Z off 70.4 30.4

Here’s a full, functional example:

import "csv"

csvData = "
#group,false,false,false,true,false
#datatype,string,long,dateTime:RFC3339,string,string
#default,_result,,,,
,result,table,_time,_field,_value
,,0,2021-01-01T00:00:00Z,light,on
,,0,2021-01-01T00:15:00Z,light,off
,,0,2021-01-01T00:30:00Z,light,off

#group,false,false,false,true,false
#datatype,string,long,dateTime:RFC3339,string,double
#default,_result,,,,
,result,table,_time,_field,_value
,,1,2021-01-01T00:00:00Z,temp,70.1
,,1,2021-01-01T00:05:00Z,temp,70.1
,,1,2021-01-01T00:10:00Z,temp,70.5
,,1,2021-01-01T00:15:00Z,temp,70.4
,,1,2021-01-01T00:20:00Z,temp,70.1
,,1,2021-01-01T00:25:00Z,temp,70.1
,,1,2021-01-01T00:30:00Z,temp,70.5
,,1,2021-01-01T00:35:00Z,temp,70.4
,,2,2021-01-01T00:00:00Z,pressure,30.1
,,2,2021-01-01T00:05:00Z,pressure,30.1
,,2,2021-01-01T00:10:00Z,pressure,30.5
,,2,2021-01-01T00:15:00Z,pressure,30.4
,,2,2021-01-01T00:20:00Z,pressure,30.1
,,2,2021-01-01T00:25:00Z,pressure,30.1
,,2,2021-01-01T00:30:00Z,pressure,30.5
,,2,2021-01-01T00:35:00Z,pressure,30.4
"

csv.from(csv: csvData)
    |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
    |> sort(columns: ["_time"])
    |> fill(column: "light", usePrevious: true)
1 Like

Thanks for the detailed explanation and sample code, I was able to get this working in no time!