The only down-side is it will not work correctly when looking back half a year (from daylight-saving time to non-daylight-saving or vice versa). But for what we use Grafana for, it is enough now.
The official solution “Use only UTC in your databases” is IMHO arrogant, but I understand Grafana was build on some idea and some world-view which not necessarily correspond with all users.
That down-side is exactly the reason that virtual everyone putting timestamps into a database anywhere in the world and for any application, whether it is your bank keeping track of transactions or for real time process monitoring such as Grafana, puts timestamps into their database in UTC. Whether it is arrogant to suggest that you should adopt a solution that works is up to you to decide.
Our company is running for 20 years without need of time zones (we are not so “international”). That is why my database and all connected applications use local time. Now we have 10+ systems reading from the database, number of third-party inputs, 250 devices generating data and even users reading data from DB directly. I’m afraid it is not possible to change the time saving without causing mayhem in our production
As I was happy yesterday about the workaround, it turned out I cannot use it in timefilter and timegroup macros and without the macros, I cannot use fill value and so on… That is half of Grafana features used in our dashboards.
I’ll take some time to calm down and then we will have to decide if to search for another workaround, change all our DBs and apps to UTC or stop using Grafana as “not suitable for our environment”. The third option would be the worst, because until yesterday I really loved Grafana.
What are you going to show on the morning of the day that the clocks go back, for the time between 01:00 and 02:00? There will be two samples for each timestamp in that interval.
Well, nothing. The plant is not running during every night from Saturday to Sunday.
The same problem would be the length of shift for workers. When you have 8-hour shifts, you don’t want to care about “why was my shift longer this weekend?”.
OK, so as you can see you have very unusual requirements. No doubt you see why most use UTC (or at least a fixed time zone) for such things. You will just have to hope that the company does not get so successful that they have to start working night shifts.
One way to ease the pain of changing would be to add a UTC timestamp column to the database and modify the application writing the data to write both timestamps, then run an update on the db which fills in the new timestamp for the historical data. Then you will have both timestamps so existing applications will continue to work. Over time you could, if you want to, modify the applications to use the new timestamps and eventually remove the old one.
Well my requirement is “do not expect my data as something else” and “skip recalculating data back and forth when everything is in the same time-zone”.
I understand the pros of UTC. The con is, you have to recalculate the time for every input and output when everything you use is in the same time-zone. We will have to discus the transformation of our systems with my colleagues and maybe there will be enough courage to change it once and for all.
The same occurred in my mind during lunch break. I can even use trigger on insert to fill UTC column without reworking inserting applications. I’m just not a fan of doing this “just for Grafana”. We’ll see if we agree on changing the local time to UTC globally in all connected applications.