Unexpected timestamp when using aggregated window per day

InfluxDB 2
,
1

Hello,

I am quite new to using InfluxDB (and also use Grafana in addition), but hereit is about pure InfluxDB behaviour.

I am trying to query some data using Flux in the Data Explorer. I am working with an aggregated window and try to get the last value of a day.

When setting the aggreation to 5s (seems to be the minimum), I have the individual values around the day change.

If I now change the aggregation to 24h / 1d, I do get this:

image
image2024×214 20.5 KB

The values shown seem correct for me, but I would expect the date to be 30th of September, because I expect to have the last value of that day. So I would want the break point to be 23:59:59 on 30th of September rather than 00:00:00 on 1st of October. Is that somehow possible?

Thanks and regards,
Matthias

2

oh, nobody has an idea how I can actually solve this problem?

3

@Matthias182 By default, aggregateWindow() has a timeSrc parameter that defines the column to pull the new time for the aggregated point from. By default, this is _stop (the stop time of each window). To get it to behave the way you want, set timeSrc to _start:

// ...
    |> aggregateWindow(every: 1d, fn: last, timeSrc: "_start")
2 Likes
4

@scott Thanks a lot for your reply. I tried this and now I think by problem is even more complex. Data written in InfluxDB is (correctly) in UTC. So in UTC time the day break is for me at 22:00:00. This is also what the data shows as the value is reset to 0 in this example:

image
image1227×327 31.7 KB

And I would like the aggregation to work based on UTC time but show local time. So in this specific example I would want 3 as last value for the 5th of October.

Is there a possibility to achieve that?

5

@Matthias182 If I understand the problem correctly, you can just use timeShift() to shift all the timestamps a specified duration:

// ...
    |> aggregateWindow(every: 1d, fn: last, timeSrc: "_start")
    |> timeShift(duration: -2h)
6

@scott The timeshift is already known to me, but since we have the daylight saving here, the difference is not fixed but changes based on the timezones. I would need something similar that works based on the timezone rather than a fixed offset. Is there something available?

7

@Matthias182 You need to import the timezone package and use it to set the location option. This will change the behavior of aggregateWindow to account for time changes.

Note: the timezone package will not adjust timestamps to the specific timezone. It just makes it so windowing data by time can account for time changes.

import "timezone"

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

// ...
    |> aggregateWindow(every: 1d, fn: last, timeSrc: "_start")
    |> timeShift(duration: -2h)

The location name depends on the tzdata provided by your operating system. Update it to the appropriate location name.

8

Thanks, @scott, that seems to solve my issue.