I want to compute the change in temperature for each location in a given interval, say, 15 minutes, or 30 minutes. I figure that streamstats might capture the temperature value at the beginning of such time interval, using time_window to specify the interval length. But, however, the following example surprises me.
The temperature readings for Pleasonton are collected every 15 minutes, thus the following query:
| makeresults
| eval _raw="time_ Location Temperature
2021-08-23T03:04:05.000-0700 Pleasonton 185
2021-08-23T03:04:20.000-0700 Pleasonton 86
2021-08-23T03:04:35.000-0700 Pleasonton 87
2021-08-23T03:04:50.000-0700 Pleasonton 89"
| multikv forceheader=1
| eval _time=strptime(time_,"%Y-%m-%dT%H:%M:%S.%3N%z")
| fields _time Location Temperature
| sort _time
| streamstats earliest(Temperature) as previous_temp earliest(_time) as previous_time by Location time_window=5m
| convert ctime(previous_time)
I’d expect the following, as with the interval 5 minutes from an event, there is no other event, but the current one.
_time Location Temperature _raw previous_temp previous_time
2021-08-23 03:04:05 Pleasonton 185 2021-08-23T03:04:05.000-0700 Pleasonton 185 185 08/23/2021 03:04:05.000000
2021-08-23 03:04:20 Pleasonton 86 2021-08-23T03:04:20.000-0700 Pleasonton 86 86 08/23/2021 03:04:20.000000
2021-08-23 03:04:35 Pleasonton 87 2021-08-23T03:04:35.000-0700 Pleasonton 87 87 08/23/2021 03:04:35.000000
2021-08-23 03:04:50 Pleasonton 89 2021-08-23T03:04:50.000-0700 Pleasonton 89 89 08/23/2021 03:04:50.000000
but this is actually what I get:
_time Location Temperature _raw previous_temp previous_time
2021-08-23 03:04:05 Pleasonton 185 2021-08-23T03:04:05.000-0700 Pleasonton 185 185 08/23/2021 03:04:05.000000
2021-08-23 03:04:20 Pleasonton 86 2021-08-23T03:04:20.000-0700 Pleasonton 86 185 08/23/2021 03:04:05.000000
2021-08-23 03:04:35 Pleasonton 87 2021-08-23T03:04:35.000-0700 Pleasonton 87 185 08/23/2021 03:04:05.000000
2021-08-23 03:04:50 Pleasonton 89 2021-08-23T03:04:50.000-0700 Pleasonton 89 185 08/23/2021 03:04:05.000000
All taking the earliest event's temperature, which is beyond 5 minutes from any subsequent events.How can I query to get the temperature at the beginning of the time period?