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?

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 boundaries packages.

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 1d14h56m18s843ms939us.

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)