I got it with this query from influxdb: SELECT “kW” FROM “file” WHERE “day”=~ /kas.lan-20191224*/ FILL(null)
Timezone in db is correct. Timezone in the file (csv) this is read from is also correct. But in grafana I get or the wrong data or the wrong timezone. When I select browser time settings I get this graph:
This has the correct timezone (for my location) at the bottom but the data drawn in the graph is from an hour ago. When I select UTC in the settings I get the correct data BUT an incorrect time zone. Either way whatever I choose I see data from an hour ago.
On all machines that collect this data, store this data and that run grafana have the same date and timeformat. I also have chronograf and this displays the correct data for the correct timestamp/timezone. Can anyone tell what is happening?
When you say that the timestamps you show in the first post, such as 2019-12-24T14:10:00Z, are correct, do you mean that the time of that sample was 14:10 UTC?
What timezone is your grafana server configured for and what timezone is the machine running the browser configured for?
I mean by that, that the data is recorded on that time (my local time). What happens is that during the day the sun comes up and SBFSpot reads the data from an invertor and puts that data into a csv. Telegraf puts the data from that csv into influxdb. The data inside the csv is written in the timezone Europe/Brussels. The timestamps inside influxdb is also Europe/Brussels (AFAIK). But grafana doesnt show the most recent data OR the correct timestamp for the data. I also dont know if this is an issue since the timechange from summer hour to winter hour OR this has been a longer issue.
Browser:
$ ls -l /etc/localtime
lrwxrwxrwx 1 root root 35 Oct 29 22:30 /etc/localtime -> /usr/share/zoneinfo/Europe/Brussels
lrwxrwxrwx 1 root root 27 Nov 18 01:00 /etc/localtime -> /usr/share/zoneinfo/Etc/UTC
root@oy:~# docker exec -it influxdb date
Fri Dec 27 00:06:49 CET 2019
I guess this is the issue? I dont get it. I didnt look further into this as the date “Fri Dec 27 00:06:49” is local to me (or how do you say that). A quick google search says me that UTC is one hour behind me so why is the docker not in utc? Does it use the tz from the host or something?
That is wrong. 2019-12-24T14:10:00Z 0.096 - that ‘Z’ in the timestamp mean zero timezone = UTC. Always write data to InfluxDB in the UTC and you won’t have a problem with timezones and visualization.
Did you consider how graphs will looks like during daylight saving time (DST) clock shifts, when you have data in local timezone (double data/no data)? DST happens at different data in different countries = another complication if you want to present graph in the user local timezone for global users, when you use local data timezone.
Use UTC and let Grafana to manage timezone in the graph.
Or hack it on the InfluxDB query level (tz()), but it will be only workaround for one particular problem and you may expect more problems.
Tomorrow evening I will try to convert the timestamp for the solar installation to utc. But what about metrics from hosts (which I also collect)? A small test tells me that time and metrics are correct. Where does the conversion happen in this case? Is it telegraf that sends cpu, mem, netw metrics to the db with a utc timestamp or does telegraf just send the metrics and influxdb adds the timestamp?
Is this issue resolved?
When I run in my shell: docker exec -it grafana date
I get: Sun Feb 16 18:50:07 UTC 2020
But the real date is: Mon March 2 18:08 ....
This means that the wrong date is also saved when I update a dashboard
I also couldn’t set the right date with: $ docker exec -u 0 -it grafana date -s "2020.03.02-18:20:00" date: can't set date: Operation not permitted Mon Mar 2 18:20:00 UTC 2020
