I am on my first steps with flux right now and I cannot solve this issue, maybe someone could help me to understand it.
I’ve got a shelly plug that reports a total energy value, it’s an increasing value. So I tried to find the daily max and then I want to compare this to the day before, so I can see the daily increase as a bar diagram.
from(bucket: "balkonpv")
|> range(start: -14d)
|> filter(fn: (r) => r._measurement == "energy")
|> aggregateWindow(every: 1d, fn: max, createEmpty: true)
|> difference(nonNegative: false, keepFirst: true, columns: ["_value"])
My issue: The diagram got a 1 day shift. There is data from 07/02 (value=0), so the first bar should have the value 3.99kWh on 07/03, but it’s on 07/04 and I see two bars for the current date (diagram created on 11.07.), which is wrong too. 5.94kWh should be the 07/09 and the value on the current day should increase while the time proceeds.
Any ideas? Thanks in advance!
Best,
Ray
working with time series gets tricky when you try to get stats per day.
first think to take into account is the timezone, influxdb uses UTC, so this line
|> aggregateWindow(every: 1d, fn: max, createEmpty: true)
may be grouping the start and end of each day different to your start of the day. you can specify a time zone or a time offset. but if you use time offset then there is the daylight savings time that eventually messes up everything. using time zone requires zoneInfo package to be installed.
I think your problem may be because of the UTC vs local times.
hey @fercasjr, thanks for the help, timezone issues seem to be a good hint. I tried this now:
import "timezone"
option location = timezone.location(name: "Europe/Berlin")
That way the x-axis descriptione moved from 02:00 to 00:00 for each day, but the bars are still the same, data is shifted a day and I get two bars for today. Is that the right way to use the timezone in the query?
More details might help:
The first recorded value is from 03.07.2023, that’s the day the plug went online. To get a difference for that first day I manually added a value=0 for 02.07.2023, so the diff between that day and the max on 03.07. is that 3.99kWh displayed for the 04.07.
Thanks,
Ray
Does no one have a idea about this? I corrected the timezone but this only fixed the displayed time, not that the date is wrong and 1 day off. See the current state, the last bar should be today (which is correct), but the bar before should be for 28th of July, not todays date. Thanks a lot!
I believe that changing your aggregateWindow line to the following one might solve the issue you are facing:
|> aggregateWindow(every: 1d, fn: max, createEmpty: true, timeSrc: "_start")
