Daily task in different timezone

I need to aggregate data for a “daily” statistic. I need to store the daily values long-term, but also use them to visualize the most recent days. Basically a graph that displays the daily values for the last 2 years, including yesterday.

I understand that Flux comes with time zone support so the aggregation per-day should run smoothly.
However the task configuration is outside Flux context: In the “every” property I would use “1d”. But this would relate to UTC, not to my timezone.

This means that the value for yesterday will only be available after several hours (depending on timezone).

Is there a proper solution for this?

Sources I read:

Hello @ypnos,
Good question!
You can use the offset to delay the execution of the query in the task configuration options. Then you can explicitly define the range relative to UTC so that you can make sure you’re capturing it (so specifying range(start: , stop: ) as opposed to range(start: -task.every)).

Is that clear? Working with timezones and thinking about time is so tricky.

Also ps I like your username + sleepy cat combo :stuck_out_tongue:

1 Like

Thanks for your reply, @Anaisdg!

So this means I need to update the tasks whenever the timezone changes, but also whenever the offset of the timezone changes (due to daylight savings). I can either do that or run the query manually once per day.
I guess I have a pretty clear picture on how to proceed.

p.s.: Thanks :blush: :smile_cat:

1 Like

That is really annoying to have to change the tasks, with every winter/summer time change.
Isn’t there a more elegant way to do this?
It would be so more simple if the task could start using the local time setting of the OS. But what I have seen, that even using the option to configure this with Cron, it still using the UTC-times (as opposite when you would use Cron on the OS).

There should be an elegant/simple way of doing this. It really causes a negative impact when you have to take care of changing tasks as well even the time zone changes. Developers should have a look into this.

Yes, this is really cumbersome.
I now changed my Tasks to look like this:

import "timezone"
import "experimental"

option location = timezone.location(name: "Europe/Amsterdam")

option task = {name: "DailyTask", cron: "0 0 * * *", offset: 2m}

offset = 2h
midnight = experimental.subDuration(d: offset, from: today())

data =
    from(bucket: "inputbucket")
        |> range(start: -2d, stop: midnight)
        |> filter(fn: (r) => r["measurement"] == "my_measurement")

data
    |> aggregateWindow(
        every: 1d,
        fn: last,
        createEmpty: false,
    )

This seems to work when done interactively.
By changing the “offset” variable value it can be changed to account for the winter/summer time.

If there would be a way to get the “offset” automatically from the Timezone, or out of the database (and update that based on the winter/summer time) this could automated to change with summer/winter-time.
But I’m not a programmer and rather new to Flux, so wouldn’t know how to that.
Still thought, this is annoying not to have this basic functionality in Flux.

We were able to make it work with cron option so that local timezone is respected. However there is a bug so on first time, the task runs at UTC. From second time on, it will run at the correct time (timezone-aware).

We’re still not perfectly happy with it, so currently we keep 48 values per day (every 30 minutes) so that we will always be able to compute a daily figure for any timezone.

The task didn’t run properly :frowning:
After experimenting I now found out that if you use:
option location = timezone.location(name: "Europe/Amsterdam")
The Today() and Now() functions shift the time and add the Timezone offset to the time!
This is completely undocumented.

So in my case 00:00 UTC = 02:00 Local time.
This means however if you use this as I did to select a range, this is compared to the UTC-time-stamps of the data, which then of course gives you the complete wrong range.

Anyway I have now changed the Task as follows:

import "timezone"
import "experimental"

option task = {name: "SolarDaily", cron: "0 0 * * *", offset: 2m}

startoffset = 26h
tzoffset = 2h

start = experimental.subDuration(d: startoffset, from: today())
stop = experimental.subDuration(d: tzoffset, from: today())

data =
    from(bucket: "telegraf")
        |> range(start: start, stop: stop)
        |> filter(fn: (r) => r["measurement"] == "my_measurement")
data
    |> aggregateWindow(
        every: 1d, 
        fn: last, createEmpty: false, 
        location: timezone.location(name: "Europe/Amsterdam")
        )

Now see if this works tomorrow…

Mmm, it can be even more buggy: I have 4 tasks that run with ‘cron’, and yesterday only 1 of them actually ran at 00:00 local time, the others still (after 3 times) use 00:00 UTC.
Documentation gives no clarity how this should work, but this is buggy for sure.
What a mess!

I don’t know how, but without any changes from my side, after 3 days the tasks all started to run properly on 00:00 local time using the cron option.

1 Like

Hey Marco,
I have the same issue. Did you use an offset or does it work without it? May it be possible for you, to post the code? It would really help me.

Sure, this is how I finally created the task to write the total usage for each day and the last meter reading into a “daily” bucket:

import "timezone"
import "experimental"

option task = {name: "SolarDaily", cron: "0 0 * * *", offset: 2m}

option location = timezone.location(name: "Europe/Amsterdam")

startoffset = 2d
tzoffset = 0h
start = experimental.subDuration(d: startoffset, from: today())
stop = experimental.subDuration(d: tzoffset, from: today())

data =
    from(bucket: "telegraf")
        |> range(start: start, stop: stop)
        |> filter(fn: (r) => r["measurement"] == "solar")
        |> filter(fn: (r) => r["topic"] == "import")

data
    |> aggregateWindow(every: 1d, fn: last, createEmpty: false, location: timezone.location(name: "Europe/Amsterdam"))
    |> map(fn: (r) => ({r with _endvalue: r._value}))
    |> difference(nonNegative: true, columns: ["_value"], keepFirst: false)
    |> map(fn: (r) => ({usage: r._value, endvalue: r._endvalue, _time: r._time, _measurement: "solar_daily"}))
    |> timeShift(duration: -1d)
    |> experimental.to(bucket: "meters_daily")

By using the
option location = timezone.location(name: "Europe/Amsterdam")
the today() function started to use the local time, so the offset can be 0 (and hopefully does not have to be changed when switching between summer/winter time).

I have added the timeShift at the end so it uses the 00:00 local time of the day for which the totals where calculated as the timestamp for the measurement, instead of 00:00 local time of the “next” day.

2 Likes