# Aggregating by day and respecting timezones

**URL:** <https://community.influxdata.com/t/aggregating-by-day-and-respecting-timezones/17429>\
**Category:** Welcome & Getting Started\
**Created:** [January 1, 2021, 1:40pm UTC](https://community.influxdata.com/t/aggregating-by-day-and-respecting-timezones/17429 "2021-01-01T13:40:17Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![chris\_b](https://avatars.discourse-cdn.com/v4/letter/c/43a26b/32.png) [@chris\_b](https://community.influxdata.com/u/chris_b)\
**Post date:** [January 1, 2021, 1:40pm UTC](https://community.influxdata.com/t/aggregating-by-day-and-respecting-timezones/17429/1 "2021-01-01T13:40:17Z")

</div>

Hi,  
I’m using InfluxDB2 with Flux but that is probably also valid for previous versions.  
When trying to get daily aggregated values, a day seams to be defined as a day in UTC time. But I’m in UTC+1 so I’m always getting the sums from 01:00 day1 to 01:00 of day2. Is there a way to change this behavior?

Example:

```
from(bucket: "example-bucket")
|> range(start: -1w)
|> filter(fn: (r) =>
  r._measurement == "example-mesurement" and
  r._field == "some-continuous-growing-value"
)
|> aggregateWindow(every: 1d, fn:max)

```

The aggregation won’t stop at 24:00 my time of a given day but will run till 1:00 of the following day (which is correct in UTC time)

---

<div class="post-metadata">

**Author:** ![Anaisdg](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/anaisdg/32/6401_2.png) [@Anaisdg](https://community.influxdata.com/u/Anaisdg)\
**Post date:** [January 11, 2021, 4:34pm UTC](https://community.influxdata.com/t/aggregating-by-day-and-respecting-timezones/17429/2 "2021-01-11T16:34:26Z")

</div>

Hello @chris_b ,  
Welcome!  
Here is documentation on manipulating timestamps. I believe many funcitons outlined there will be useful to you.

> **[Manipulate timestamps with Flux | InfluxDB OSS 2.1 Documentation](https://docs.influxdata.com/influxdb/v2.0/query-data/flux/manipulate-timestamps/)**
>
> Use Flux to process and manipulate timestamps.

If I’m understanding you correctly I believe something like this would help:

```auto
import "system"
import "experimental"
mytime = system.time()
myrange = experimental.subDuration(
  d: 7d,
  from: mytime,
)
...
|> range(start: myrange)

```

---

<div class="post-metadata">

**Author:** ![chris\_b](https://avatars.discourse-cdn.com/v4/letter/c/43a26b/32.png) [@chris\_b](https://community.influxdata.com/u/chris_b)\
**Post date:** [January 11, 2021, 8:45pm UTC](https://community.influxdata.com/t/aggregating-by-day-and-respecting-timezones/17429/3 "2021-01-11T20:45:53Z")

</div>

Hi @Anaisdg ,  
thanks for your reply. Adjusting the range is not really an issue. My biggest concern is handling the `aggregateWindow(every: 1d, fn:max)`.  
In my case I have a datasource that accumulates daily values (energy generation in this example) that resets every day on 0:00 to zero. Unfortunately I have no control over this behavior and therefore can’t change it.  
Currently the aggregateWindow messes up the data if I aggregate the daily max values if the aggregation window doesn’t match the reset window of the data source.

At the moment I’m working around this with `|> timeShift(duration: -1h)` but this is probably unreliable as I expect this to mess up the data as soon as we enter the daylight saving time again…

I assume that the implementation of `option location` ([Options | Flux 0.x Documentation](https://docs.influxdata.com/influxdb/v2.0/reference/flux/language/options/)) would be able to handle this behavior.

For what ever reason this was not problem with the according InfluxQL statement. Some magic took care of the timezones:  
`SELECT max("some_value") FROM "some_measurement" WHERE $timeFilter GROUP BY time(1d) fill(null)`

---

<div class="post-metadata">

**Author:** ![tomn](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/tomn/32/7289_2.png) [@tomn](https://community.influxdata.com/u/tomn)\
**Post date:** [April 25, 2021, 7:16pm UTC](https://community.influxdata.com/t/aggregating-by-day-and-respecting-timezones/17429/4 "2021-04-25T19:16:43Z")

</div>

Also looking for a solution here. I want to run the aggregate function over set day windows. I tried setting the start time exactly to the start of the 24hour window I want to use but the timestamps returned still use UTC midnight as the time value.

Also don’t want to rely on time shifting if possible - doesn’t seem like the clean solution.

---

<div class="post-metadata">

**Author:** ![Henjoe\_Gutierrez](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/henjoe_gutierrez/32/5157_2.png) [@Henjoe\_Gutierrez](https://community.influxdata.com/u/Henjoe_Gutierrez)\
**Post date:** [June 20, 2022, 1:29pm UTC](https://community.influxdata.com/t/aggregating-by-day-and-respecting-timezones/17429/5 "2022-06-20T13:29:28Z")

</div>

Hello,

I wonder if you found solutions for this.  
I am using influxQL at V1.8 and posted a question similar to this.

> [@InfluxDB OSS 1.8 invalid data for different sampling](https://community.influxdata.com/t/influxdb-oss-1-8-invalid-data-for-different-sampling/25476):
>
> Hi All, I just actually notice that there is some discrepancy that I have seen in my influxDB 1.8, I just noticed it in my grafana dashboard. So here is a scenario. I tried to view a Trend graph of same measurement from my influxdb to my grafana. I add 2 queries for the same measurement, one is group by time (1d) and the other is group by (1h). Using the “first” selector in both queries, here is what I have notice. The value of my hourly (1h) at exactly 8 am is the same as the value of 1day…

---

<div class="post-metadata">

**Author:** ![medeirox](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/medeirox/32/12684_2.png) [@medeirox](https://community.influxdata.com/u/medeirox)\
**Post date:** [February 15, 2023, 11:25pm UTC](https://community.influxdata.com/t/aggregating-by-day-and-respecting-timezones/17429/6 "2023-02-15T23:25:15Z")

</div>

After hours of debugging, I’ve found that you have to add a “compensation” to the timezone on the aggregateWindow function using the [offset](https://docs.influxdata.com/flux/v0.x/stdlib/universe/aggregatewindow/) parameter. Not straightforward, but seems to be working. You can try it out.  
For an offset of GMT-03:00 you should add the difference, so:  
aggregateWindow(every: 1d, fn: sum, offset: 3h)

---

<div class="post-metadata">

**Author:** ![lucasbatier](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/lucasbatier/32/16076_2.png) [@lucasbatier](https://community.influxdata.com/u/lucasbatier)\
**Post date:** [August 6, 2024, 8:48am UTC](https://community.influxdata.com/t/aggregating-by-day-and-respecting-timezones/17429/7 "2024-08-06T08:48:39Z")

</div>

It is manageable by the location argument [aggregateWindow() function | Flux Documentation](https://docs.influxdata.com/flux/v0/stdlib/universe/aggregatewindow/#location) in aggregateWindow().

Here is an example with Paris timezone:

```auto
from(bucket: "example-bucket")
|> range(start: -1w)
|> filter(fn: (r) =>
  r._measurement == "example-mesurement" and
  r._field == "some-continuous-growing-value"
)
|> aggregateWindow(every: 1d, fn:max, location: {zone: "Europe/Paris", offset: 0h})

```

NB: It also managed the Daylight Saving Time properly