How do I set the Grafana time for the calendar to match my GMT-3 time zone? For example if I put the last day it is D-24 + 3h the right would be D-24 only, now it is 9:05 am it is from yesterday 12:05 to today 12:05, the right would be from yesterday 9:05 to today 9:05. The time should now be 09h05 and mark in the graphan 12h05. How to configure this in custon.ini?
Make sure the machine running the browser is set for the correct timezone. Then check in the Dashboard Settings that you have the Timezone set to use the Browser local time.
Having done that then if you are using the chart node and ask for the the last 3 hours, for example, then the time shown against the axis at the right hand side should be the current local time.
Once that is correct, if the data points are not showing at the correct point on the graph then that means that the timestamps in the database are not in UTC, which they must be.
The Server has the correct time (GMT -3), when I use the browser settings, the calendar is correct, but the value passed in the variables __timeFrom() and __timeTo() comes with the GMT date, so I have to get it right querie Dateadd (hour, -3h, $__timeFrom()). The Chart also shows GMT-0. I’m using MSSQL for queries.
In that case almost certainly the timestamps in the database are not in UTC. I don’t know anything about MSSQL though, so don’t know how how you can determine whether that is true. Alternatively perhaps the timestamp column is the wrong type. You would need someone knowledgeable in MSSQL to know that.
SELECT 'SYSDATETIME() ', CONVERT (time, SYSDATETIME());
SELECT ‘SYSDATETIMEOFFSET()’, CONVERT (time, SYSDATETIMEOFFSET());
SELECT 'SYSUTCDATETIME() ', CONVERT (time, SYSUTCDATETIME());
SELECT 'CURRENT_TIMESTAMP ', CONVERT (time, CURRENT_TIMESTAMP);
SELECT 'GETDATE() ', CONVERT (time, GETDATE());
SELECT 'GETUTCDATE() ', CONVERT (time, GETUTCDATE());
|SYSDATETIME() |10:48:40.3274263|
|SYSDATETIMEOFFSET()|10:48:40.3274263|
|SYSUTCDATETIME() |13:48:40.3274263|
|CURRENT_TIMESTAMP |10:48:40.3230000|
|GETDATE() |10:48:40.3230000|
|GETUTCDATE() |13:48:40.3270000|
GMT -3 => 10h48m
GMT => 13h48m
@alexandrefrp do you have the same timezone set up on your client machine?
@alexandrefrp That doesn’t tell us anything about the data stored in the db.
Yes, all clients have the same time zone GMT-3
The columns that are used in the date filter in MSSQL are of the datetime type stored with local time (GMT -3).
As I said, that is the problem, they should be in GMT.
So I have to save the data in the GMT-0 standard in the database where the servers are in GMT-3 and the official time in my country is GMT-3? This does not seem right, I should be able to change which standard GMT I wanted to use in Grafana …
Why? What is the benefit? Remember that computers work in UTC, not local time. If you change the time zone on your PC it displays a different local time, but when changing the zone it does not change the actual time clock in the PC, which is in UTC, it just changes the way the time is displayed. So if you want to save the time in local time it takes the computer more work as it has to convert the UTC time to local.
Also consider what would happen if your UK branch wanted to add data into the db? They would have to convert it to your timezone. Even worse suppose your company moved to a different zone, what would you do then? You may say you don’t care because those issues don’t apply to you, but they do apply to many many organisations. Your bank, for example, has to record every transaction around the world in a way that is consistent. If it stored transactions in the different countries in different timezones then disaster would very soon ensue.
All serious databases around the world store timestamps in UTC, so to allow grafana to be able to use local timezone timestamps you are asking for additional complexity to be added. So the question is, what is the benefit of storing timestamps in a non-standard way?
2 Likes
Okay, so what should I do to get around this display in grafana would be to create a datetime column in the GMT standard, or do a dateadd -3h in the where clause in the date column compared to the __timeFrom and __timeTo variables?
I did the test and it works by placing browser settings and making this adjustment in the query…
SELECT DATEADD(hh, 3, [data_hora]) as time
,[matricula] as metric
,count(*) as Qtde
FROM MyTable_GMT-3
WHERE DATEADD(hh, 3, [data_hora]) BETWEEN __timeFrom() AND __timeTo()
GROUP BY DATEADD(hh, 3, [data_hora]), [matricula]
Can’t you fix the database timestamps or is this a legacy database?
I have never had to overcome the problem so I don’t know what the best workaround would be.
Hi, we have a timestamp on a MSSQL database which is not in UTC, and as we don’t have access to correct this issue, we found a workaround is to make a SQL that can return the dates and ranges with the correct time to be displayed in Grafana.
First, we changed __timeEpoch() into DATEDIFF and DATEADD. Here is my example with UTC-5 time zone. Note that the first is positive since the difference is to get Epoch Time, and the BETWEEN statement is negative since the function __timeFrom() has different format.
There are useful documentation on dates at: https://docs.microsoft.com/en-us/sql/t-sql/functions/date-and-time-data-types-and-functions-transact-sql?view=sql-server-ver15
Original Query:
SELECT
$__timeEpoch([timestamp_field]),
FROM
[database]
WHERE
$__timeFilter([timestamp_field])
ORDER BY
[timestamp_field] ASC
Modified Query for Timestamps in UTC-5
SELECT
DATEDIFF(second, '1970-01-01', DATEADD(hh,5,[timestamp_field])) as time
FROM
[database]
WHERE
[timestamp_field] BETWEEN DATEADD(hh,-5,$__timeFrom()) and DATEADD(hh,-5,$__timeTo())
ORDER BY
[timestamp_field] ASC
Hope this can help.
Kind regards!
E