Select the difference between two values at specific times every day

InfluxDB 2
1

I want to select the difference between two values at specific time points (e.g. 06:00 and 18:00) or (sunset and sunrise) every day for the last week. Anyone know this select query?

2

Welcome @wheggy

Are you using Flux or InfluxQL?

3

@grant1 , I’m using influxQL.

4

Sorry, I do not have much experience with InfluxQL.

5

@grant1 Thank you! I can also use flux. Could you max be help me to do it with flux? Many thanks

6

@wheggy

Maybe something like this?

First = from(bucket: "your-bucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> hourSelection(start: 6, stop: 18)  
  |> filter(fn: (r) => r["_measurement"] == "your-measurement")
  |> filter(fn: (r) => r["_field"] == "your-field")
  |> aggregateWindow(every: 1d, fn: first)
  |> set(key: "newValue", value: "first_value_of_the_day")
  |> yield(name: "first")

Last = from(bucket: "your-bucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> hourSelection(start: 6, stop: 18)  
  |> filter(fn: (r) => r["_measurement"] == "your-measurement")
  |> filter(fn: (r) => r["_field"] == "your-field")
  |> aggregateWindow(every: 1d, fn: last)
  |> set(key: "newValue", value: "last_value_of_the_day")
  |> yield(name: "last")

union(tables: [First, Last])
  |> pivot(rowKey:["_time"], columnKey: ["newValue"], valueColumn: "_value")
  |> map(fn: (r) => ({ r with daily_difference: r.last_value_of_the_day - r.first_value_of_the_day }))
7

Just commenting on my own data that has readings populated every 10 minutes, the above query does not work as expected. The “first” value should be the value from 06:00, but instead seems to be shifted by 24 hours. The “last” value should be from 18:00, but instead seems to be shifted by 24 hours and 53 minutes. So there is apparently something wrong with either my timestamps or the query, but maybe you can figure out if the above works correctly for your data. Maybe the date.truncate function would help here or maybe someone from InfluxDB can comment on a better solution (or the fix to my proposed one).

@Anaisdg or @Jay_Clifford – any hints?

8

@grant1 hmm that’s really interesting. I am wondering if the culprit is the aggregateWindow(). Is there any chance you could remove it and see if the hourSelection lines up as expected? My suspicion is that the aggregate window in its current form will create empty values for missing points due to the current parameters set. It will also base its aggregation on the _stop column.