Aggregate data by shift (7:00 - 7:00)

InfluxDB 2
, ,
1

I need help with the most optimal way to implement queries that aggregate data by shift lasting from 7am yesterday to 7am today.

  • query to get data from beginnig of shift till now. I know about possibilities of date package, but can’t think out how to choose start of period in a simple way (i.e. before today’s 7am I should start from yesterday’s 7am, after 7am I should start from today’s 7am);
  • query to aggregate data by shifts during current month (i.e. data for day XX should be from 7am of day XX-1 to 7am of dayXX)

I am using InfluxDB 2.7.6 and Flux

2

This is what I have so far, but not sure that this is an optimal way of solving this task:

from(bucket: "TEST")
    |> range(start: -30d)
    |> timeShift(duration: -7h)    
    |> aggregateWindow(every: 1d, fn: mean, createEmpty: false, timeSrc: "_start")
    |> timeShift(duration: 7h)
3

@ebabeshko I think you can get your desired result by specifying and offset in aggregateWindow(). This parameter shifts window boundaries by a specified duration. By default, 1d window boundaries start at 00:00:00 and end at 23:59:59:999999999, but if you add a 7h offset, the window boundaries shift to 07:00:00 and 06:59:59.999999999.

from(bucket: "TEST")
    |> range(start: -30d)
    |> aggregateWindow(every: 1d, offset: 7h, fn: mean, createEmpty: false, timeSrc: "_start")
1 Like