Aggregating by day and respecting timezones

Welcome & Getting Started
1

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)

2

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

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

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

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

1 Like
4

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.

1 Like
5

Hello,

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

6

After hours of debugging, I’ve found that you have to add a “compensation” to the timezone on the aggregateWindow function using the offset 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)

7

It is manageable by the location argument aggregateWindow() function | Flux Documentation in aggregateWindow().

Here is an example with Paris timezone:

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

1 Like