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
@Anaisdg , @scott Hey both, any guidance here please?
@ajetsharwin You should just be able to use the
offset parameter of
|> aggregateWindow(every: 1d, fn: last, offset: 16h)
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)?
@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
offset = duration(v: int(v: now()) - int(v: boundaries.yesterday().start))
|> aggregateWindow(every: 1d, fn: last, offset: offset)
Quick note about this , this method will give you nanosecond precision on the offset duration, so you’ll get a window offset that may be something like
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,