# Aggregate Window, Custom timeframe

**URL:** <https://community.influxdata.com/t/aggregate-window-custom-timeframe/30910>\
**Category:** InfluxDB 2\
**Tags:** flux, aggregate\
**Created:** [July 27, 2023, 2:06pm UTC](https://community.influxdata.com/t/aggregate-window-custom-timeframe/30910 "2023-07-27T14:06:16Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![ajetsharwin](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/ajetsharwin/32/13686_2.png) [@ajetsharwin](https://community.influxdata.com/u/ajetsharwin)\
**Post date:** [July 27, 2023, 2:06pm UTC](https://community.influxdata.com/t/aggregate-window-custom-timeframe/30910/1 "2023-07-27T14:06:16Z")

</div>

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 🙏

---

<div class="post-metadata">

**Author:** ![ajetsharwin](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/ajetsharwin/32/13686_2.png) [@ajetsharwin](https://community.influxdata.com/u/ajetsharwin)\
**Post date:** [July 31, 2023, 11:32am UTC](https://community.influxdata.com/t/aggregate-window-custom-timeframe/30910/2 "2023-07-31T11:32:28Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![scott](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/scott/32/16492_2.png) [@scott](https://community.influxdata.com/u/scott)\
**Post date:** [July 31, 2023, 11:24pm UTC](https://community.influxdata.com/t/aggregate-window-custom-timeframe/30910/3 "2023-07-31T23:24:22Z")

</div>

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

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

```

---

<div class="post-metadata">

**Author:** ![kose163](https://avatars.discourse-cdn.com/v4/letter/k/ee59a6/32.png) [@kose163](https://community.influxdata.com/u/kose163)\
**Post date:** [August 1, 2023, 7:33am UTC](https://community.influxdata.com/t/aggregate-window-custom-timeframe/30910/4 "2023-08-01T07:33:19Z")

</div>

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)?

---

<div class="post-metadata">

**Author:** ![scott](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/scott/32/16492_2.png) [@scott](https://community.influxdata.com/u/scott)\
**Post date:** [August 1, 2023, 2:57pm UTC](https://community.influxdata.com/t/aggregate-window-custom-timeframe/30910/5 "2023-08-01T14:57:51Z")

</div>

@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`](https://docs.influxdata.com/flux/v0.x/stdlib/experimental/date/boundaries/) packages.

```javascript
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 ☝, this method will give you nanosecond precision on the offset duration, so you’ll get a window offset that may be something like `1d14h56m18s843ms939us`.

---

<div class="post-metadata">

**Author:** ![kose163](https://avatars.discourse-cdn.com/v4/letter/k/ee59a6/32.png) [@kose163](https://community.influxdata.com/u/kose163)\
**Post date:** [August 5, 2023, 12:41pm UTC](https://community.influxdata.com/t/aggregate-window-custom-timeframe/30910/6 "2023-08-05T12:41:55Z")

</div>

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