Data is showing in UTC time, changing dashboard to UTC time works ok, however other dashboard are now off…

Any suggesting on how to get the timezone correct?

Strange. Maybe you have some custom timezone setting in your grafana organization or in your grafana user preferences?

Marcus

well it’s not working again. I upgraded to 5.1, and somehow the the time is off again. Played with setting Dashboard timezone and user pref timezone, no luck…

SELECT
Chktime as time,
lag_in_seconds as P3
DELAYSTATS
WHERE
Chktime >= DATEADD(s, 1524604590, ‘1970-01-01’) AND Chktime <= DATEADD(s, 1524611696, ‘1970-01-01’) and replica_server_name like ‘USHOU%3%’
ORDER BY
CHKTIME ASC

SQL Management Studio server is returning on the same workstation
|2018-04-24 22:23:00.917|139|
|2018-04-24 22:24:00.173|148|
|2018-04-24 22:25:00.900|183|
|2018-04-24 22:26:00.810|200|

Grafana 5.1 Table Shows
2018-04-24 17:23:00 139.00
2018-04-24 17:24:00 148.00
2018-04-24 17:25:00 183.00
2018-04-24 17:26:00 200.00

What timezone of sql server? What timezone of grafana server?

Seems like you have browser time as timezone setting in Grafana. Please include screenshot of your dashboard timezone setting, user preferences and possibly organization preferences.

Marcus

Since you’re using Local browser time I guess that you’re timezone setting in your browser/computer is 5 hours off from UTC and that’s why you see the result. If you want the dates to be shown in UTC please change your dashboard settings timezone to UTC.

Marcus

mefraimsson:

f from UTC and that’s why you see the result. If you want the dates to be shown in UTC please change your dashboard settings timezone to UTC.

Changing my dashboard to UTC works for the SQL panel, however all the other panels are then ahead by 5 hours.

MS SQ Mgmt studio return the correct tiemzone adjusted time, however it looks like the grafana SQL plugin is not taking into account timezone…

So then your other datasources are not running on server with UTC? What datasources are these? Feels to me that they will show weird results even when you’re using Local browser time .

Marcus

Its now 6:20am CST locally - UTC=12:20PM

Dashboard Set to UTC: Sqlplug returns data at 2018-04-26 06:20:01 (other data is UTC -5)
Dashboard Set to Default/Browser: Sqlplug returns 2018-04-26 01:20:01 (other data is CST)

MSSQL Studio returns data in CST correctly

You didn’t answer my earlier questions.

So then your other datasources are not running on server with UTC? What datasources are these?

That’s your problem

Marcus

Sounds to me that your Grafana instance also running on a server having same timezone as graphite then (CST).

First of all you should always run Grafana on server with UTC timezone. Second, all your datasources should also run on server with UTC. If not doing this you’ll end up with unexpected results.

Marcus

I think I can be more helpful here than some of the previous responses. If not for the OP but for those who will surely come later searching with the same issue. Some applications will store their time data based on their own standards be it UTC or a local time. This is a fact of life and in many instances not something that we can change. In many cases a database will have years of historical data. Expecting users to change the way their data is stored when there can be existing resources accessing this data is highly unreasonable.

That being said, I am working with some data sources in UTC and some in local time. I need to display results from both datasources on the same dashboard. Here is my solution to this UTC headache.

Note the following examples are for MSSQL
Also the following assumes you want to report in the same timezone as your MSSQL server. It should be trivial to adjust the examples below if this is not the case.

Situation 1: Data stored in UTC:
Lets calculate how many hours UTC is wrong by and store that in a variable.
Why calculate it each time and not hardcode it?
Timezones change with the seasons and we want our query to work year round.

declare @utcoffset integer;
set @utcoffset = datediff(hh,getdate(),getutcdate());

When we want to convert the stored UTC time to local time we either add or subtract the variable to get the time your report audience is expecting. In my case as I am in the US I must subtract. (notice the - sign below)

dateadd(hh,- @utcoffset ,db.datecolumn)

This is useful in where clauses to pull the records with specific times.

where db.datecolumn > dateadd(hh,- @utcoffset ,‘20180501 05:00:00’) – subtracts the calculated utc offset so you can use the time you are used to using. Here we pull records that are after 5am on 5/1/2018

Grafana wants UTC and your data is in UTC so simply select it as you normally would without a calculation

Select db.datecolumn as [time]

Siutation 2: Data stored in Local Time. (Grafana doesn’t take kindly to that)

In your where clause you can use regular time as expected but when you feed that time to grafana all your graphs are offset by however many hours UTC is off by. Not pleasant and doesn’t instill confidence in your sql-fu.

So lets calculate and store that offset time just as before.

declare @utcoffset integer;
set @utcoffset = datediff(hh,getdate(),getutcdate());

Your where clause can be simple because its stored in regular time.
where db.datecolumn > '20180501 05:00:00’

Your select statement needs math to give grafana the offset values it wants. As UTC is hours ahead of me I need to add the offset to convert local to UTC. Note that unlike the example above there is no - sign in front of @utcoffset

select dateadd(hh, @utcoffset ,db.datecolumn) as [time]

Note that if your db.datecolumn data isnt in the format dateadd is expecting you will have to cast it into the right format. (cast is the name of the command) Plenty of online resources to help with that.