Since the UK time zone change from GMT/UTC to BST (UTC+1) Grafarna has started adding +1hour to my data.
The actual source data has corrected its self and is being captured in the right time. I tried to play with the time shift options but it wouldn’t accept -1hour and even when i 1h etc it didn’t change the times …
If you see from my screen shot a process that existed before the BST change is fine , but its added +1 to the one since the change Any ideas?
I did see talk of changing things server side / globally
the data is captured with no time zone as such , it is using the server clock to “adjust”.
when i am in psql directly I see the correct value and the value I want grafarna to present , so why is grafarna adding the extra hour ? If i play with the sql to say -1hour on everything then I think this will sque the legit times being displayed before the clock change …
Please clarify - do you mean that the timestamps are in UTC, or are they
something else?
it is using the server clock to “adjust”.
That sounds bad to me. Please be clear about whether you are storing
timestamps in UTC, or not.
when i am in psql directly I see the correct value and the value I want
grafarna to present
Postgres may be applying an automatic adjustment when it displays the value to
you.
If i play with the sql to say -1hour on everything then I think this will
sque the legit times being displayed before the clock change …
You should not need to modify your query; you simply need to ensure that
timestamps of stored data are in UTC. Anything else will cause problems (one
of which is that in October, there will be two instances of 02:30 for example,
so which one do you expect to get saved in your database?).
data is in raw date_time (i guess?) no timezone . I am just piggie backing off it what is in there is not of my making and I can not change it.
grafarna is adding the extra hour on via the “timezone” selecting on the “general” page of the dashboard. Unfortunately there is nothing to say don’t use a time zone which would make this problem go away for me
Yeah. So if I set it to UTC then I still have the problem. Set it to BST and the problem goes away but this then forwards all my data before the clock change +1 hour
Obvs not SQL but I think i’ll need something like
if date between Sun, 31 Mar 2024 – Sun, 27 Oct 2024
then time +1hour
else
do nothing
In the query its self timestamp AT TIME ZONE ‘UCT’ AS timestamp_utc
Its working with Format as table if you look in that first screen shot where I have doneAT TIME ZONE ‘BST’ AS timestamp_utc , if i change that the query doesn’t work at all .
This was all working fine until the clock change !
CASE
WHEN start_time >= '2024-03-31' AND start_time < '2024-10-28' THEN start_time AT TIME ZONE 'BST'
ELSE start_time AT TIME ZONE 'UTC'
END AS start_time,
How your graph looks like for time period 2024-03-31T00:00:00Z - 2024-03-31T05:00:00Z? (Keep in mind that UK was moving timezone at 2024-03-31T01:00:00Z, not at 2024-03-31T00:00:00Z). How it will looks like if I live in central Europe (they were moving timezone at 2024-03-31T02:00:00Z)? What if you live in NY (where they changed timezone 2 weeks ago).
This is just to give you an example, why using of local timezone is a problem. So this is golden rule if you don’t want to have any problems with +/- X hours data shifts in your dashboard:
The issue with adding +1 hour to the data after changing the BST timezone may be due to the timezone settings in Grafana. Try this: Check that the time zone is set correctly in Grafana settings to UTC+1 (BST). If the settings are correct, try updating the data in your database so that it displays the correct time for the new time zone.
When I adjusted the time zone settings I could get the current data to display correctly but this then made the data prior to the clock changes and hour out . If there was a "no timezone " option I wouldn’t of had any issues
