I change the database from InfluxDB 1 to InfluxDB 2. The data from the electricity meter is stored in UTC format. I evaluate the data for days, months and years. Under InfluxDB 1 the code looks like this:
SELECT non_negative_difference(last(“value”)) FROM “stromzaehler” WHERE (“measurement” = ‘KW/h’) AND $timeFilter GROUP BY time($__interval) tz(‘CET’)
The data is evaluated here for the current day starting at 0:00 local time.
It works very well.
Now I start over under InfluxDB 2 and first compile the data for the current day.
from(bucket: “stromzaehler”)
|> range(start: today())
|> filter(fn: (r) => r[“_measurement”] == “stromzaehler”)
Unfortunately, I now had to find out that the acquisition for the current day begins according to UTC. This means that I am missing the data according to my local time of 1 hour.
How can I adapt the code accordingly so that the data is recorded from UTC+1 winter time or UTC+2 summer time?
Best Regards
Thomas
Hi @Cavekeeper, I hope you had a good weekend!
have you tried setting the location option before you query:
option location = loadLocation(name:"America/Denver")
from(bucket: “stromzaehler”)
|> range(start: today())
|> filter(fn: (r) => r["_measurement"] == “stromzaehler”)
This will change the UTC offset of your data for your query. Let me know how you get on.
Hi @Jay_Clifford
Many thanks for your fast reply.
Yes, I have already heard about this option.
I have tried it and get the following error message:
error @1:19-1:31: undefined identifier loadLocation
What version of InfluxDB 2.X are you using?
It is the docker version 2.0.9
Could you try the following instead:
import "timezone"
option location = timezone.location(name: "Europe/Belfast")
I believe there is currently a bug with loadLocation. I will need to confirm with the Flux team when they are online later.
1 Like
Yes, exactly. I had already tried this code. Unfortunately without success. Another error message:
error @1:1-1:18: invalid import path timezone
Hi @Cavekeeper ,
I see the problem occurs using InfluxDB OSS. Let me confirm with the Flux team on this and I will get back to you.
1 Like
Hello @Jay_Clifford , do you have any news?
Hi @Cavekeeper,
I was chatting with the Flux team about this issue. I am waiting for them to confirm or not if it will make it into the next release of Influx OSS. (2.1). Sorry for your wait
1 Like
Hi @Cavekeeper ,
The timezone package has made it into 2.1.1 which is now released. Please make sure you backup your data before upgrading to this release.
Thanks,
Jay
Thank you for the quick support. I did the update. The “timezone” code is now adopted. Unfortunately, the data is not made available from midnight but is still delayed by an hour. So from 1 o’clock. It didn’t change anything. The timezone code doesn’t work yet. Where could be the problem? My code looks like this:
import “timezone”
option location = timezone.location(name: “Europe/Berlin”)
from(bucket: “stromzaehler”)
|> range(start: today())
|> filter(fn: (r) => r[“_measurement”] == “stromzaehler”)
@Anaisdg have you got any ideas on this one? Was thinking timeshift might be another option but doesn’t solve the issues around seasonal changes.
Hello @Jay_Clifford
It looks like the timezone option has not yet fixed the problem. Is it now because of the option or because of my code that I only get the data listed from 1 a.m. instead of 12 a.m.? Do you have any other ideas?
Hi @Cavekeeper,
Sorry for the late reply on this one. We wanted to confirm everything internally first. It sadly looks like the feature has been disabled since it needs further work. My apologies for the frustration this has caused. You could potentially look at a time-shift . Though I do not believe this is a particularly graceful solution to your issue.
If you have a chance please launch an issue on our Github as this helps to raise the urgency for a quicker fix internally aswell.
Hi @Jay_Clifford
Thank you for your help. I have now posted a message on Github. Maybe it will help us. I will probably not want to use the timeshift function so far.
https://github.com/influxdata/influxdb/issues/22897
Thank you, your proposed solution is the way to go.
After I applied the timezone changes the time series was updated:
The query looks now like bellow:
import "timezone"
option location = timezone.location(name: "Europe/Bucharest")
from(bucket: "ha")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_field"] == "energy")
|> filter(fn: (r) => r["domain"] == "sensor")
|> filter(fn: (r) => r["entity_id"] == "emon_workstation_energy")
|> aggregateWindow(every: 1d, fn: last, createEmpty: true)
|> difference()
|> drop(columns: ["_start","_stop","domain","sensor"])
