Daily Agreggation in local Timezone

I am attempting to aggregate data on a daily basis in the specific timezone of the customer. My goal is to create a single record with a time of midnight for each function (mean, min, max, etc) for the selected day, and write this to a new bucket. My plan was to query all the data for the day (or subsection of the day) by specifying the timezone offset in the range function:

baseData = from(bucket: "loradata")
|> range(start: 2021-11-28T09:00:00-05:00, stop: 2021-11-28T21:00:00-05:00 )
|> filter(fn: (r) => r.customer == 23)

Next, I dropped the _start, _stop, and _time columns in order to map all _time fields to midnight of the specified day, as well as set _start and _stop to cover the entire day:

|> drop(columns: ["_start", "_stop", "_time"])
|> map(fn: (r) => ({ r with
_time: time(v: "2021-11-28T00:00:00Z"),
_start: time(v: "2021-11-28T00:00:00Z"),
_stop: time(v: "2021-11-28T23:59:59Z"),
}))

This works as expected, and the resulting data shows with a _time of midnight. The problem I’m seeing comes from the aggregateWindow(fn: mean, every: 24h) function, which doesn’t seem to recognize the new _time. This often results in multiple records, null values, changed dates, or other problems that prevent the |>to(...) function from properly writing the aggregated data. I would expect the _time field to be honored, but there is obviously something else going on here. Any idea what may be happening?

Data example before aggregateWindow call
5W3p8

Data example after aggregateWindow call
oh8Xn

@jimmym This is exactly what the location option is designed to manage. Try the following:

import "timezone"

option location = timezone.location(name: "America/New_York")

// The rest of your query here

Thanks.
In trying to use timezone support, I get invalid import path timezone. Does OSS 2.1 not include timezone support, or is there another cause for this import error?

Ah, the timezone package was added to Flux after the 2.1 release. If you use the nightly build or build from source, it’ll come with a newer version of Flux that includes the timezone package.

Great. I’ll pull the latest build and continue from there.

We’re still in the testing phase and plan to implement influx soon, can you offer a rough timeframe when the next official version might be released with the timezone package?

I don’t have the exact date, but I believe the next OSS release (2.2) is slated for later this month (December 2021).

1 Like