Aggregate Window, Custom timeframe

InfluxDB 2
,
1

Hi Team,

I have a database which shows the quantity of items as of each minute.
I want to refine my day to be from 1600 UTC yday to 1600 UTC today instead of 0000 yday to 0000 today.
I then want to apply aggregate functions based on the new day window definition.

The below flux query returns the last quantity observed on a given day which is midnight. How can I revise the query so it gives me the quantity of each day as at 1600 UTC?

db1= from(bucket: “quantity_analytics”)
|> range(start: 2022-12-31T15:00:00Z)
|> filter(fn: (r) => r._measurement == “quantityStats”)
|> filter(fn: (r) => r.id == “AZN”)
|> filter(fn: (r) => r._field == “quantity”)
|> keep(columns: [“_time”, “id”,“name”,“_field”,“_value”])
|> aggregateWindow(every: 1d, fn: last)

cc: @Anaisdg , @scott tagging for attention please :pray:

2

@Anaisdg , @scott Hey both, any guidance here please?

3

@ajetsharwin You should just be able to use the offset parameter of aggregateWindow():

// ...
    |> aggregateWindow(every: 1d, fn: last, offset: 16h)
4

Dear all, @scott

thanks, very helpful. I have a similar question: I would like to have a floating window which goes back from now by x. So, instead from 1600 UTC to 1600 UTC from (now - x) to now.

Is there a possibility to have a dynamic offset, for 1d something like:

|> aggregateWindow(every: 1d, fn: last, offset: now() - last midnight)

Would this work? What is the correct semantic for “now() - last midnight”?

What would be the solution for a more generic window duration (not 1d, but e.g. 30Min)?

5

@kose163 Technically you could, but I’m interested to know the actual use case. The offset parameter just needs a duration value, so as long as the expressions you use to calculate the offset returns a duration, you should be fine. You would use the boundaries packages.

import "experimental/date/boundaries"

offset = duration(v: int(v: now()) - int(v: boundaries.yesterday().start))

// ...
    |> aggregateWindow(every: 1d, fn: last, offset: offset)

Quick note about this :point_up:, this method will give you nanosecond precision on the offset duration, so you’ll get a window offset that may be something like 1d14h56m18s843ms939us.

6

Hello Scott,

thanks a lot for your reply. The actual use case is as follows (I hope I can make it clear):

I have an electric device, and I want to measure its energy consumption during the last period of d. As underlying measure, I have the cumulative energy consumption of the device.
I would like to have a graph in Grafana that shows (more or less continuously) the consumption during the last duration. The mathmatical function would be:

f(t) = v(t) - v(t-d), where:

d: is the period
t: the time
v(t’): the cumulative measurement closest to time t’

To make it a bit more complex, v(t) is not a continuous function, it has values every 5-10 Minutes. V(t) should select the closest value to t. Also, there are several outliers which do not allow to use “spread”. f(t) should e as continuous as possible (no measurement evey d datapoints)

I was thinking of something like:

|> aggregateWindow(every: v.windowPeriod, period: 24h, fn: last, createEmpty: false) - aggregateWindow(every: v.windowPeriod, period: 24h, fn: first, createEmpty: false)

but as a beginner, I don’t get the syntax right. Could you help?

Thanks a lot,

kose