- What Grafana version and what operating system are you using?
- v8.2.1 (88622d7f09). Win10
Data source is SQL Server 2014 (Read only).
I need a simple timeseries graph with data from now-1h to now.
The actual UTC time of the event is:
changeDate datetime2(7) . I’m using DBeaver configured in CET (I am in UTC+1 timezone).
2022-01-27 13:47:09 (CET, UTC +1)
2022-01-27T12:47:09.784Z (value displayed as source code in ISO8601 format, so UTC)
Everything seems ok, it´s the actual time of the event.
Then I check the received data in Grafana configured in CET (UTC +1) time zone.
(raw data in Query Inspector>Data and “Formatted data” turn off)
1643291229000 or GMT(UTC):
2022-01-27 13:47:09 (shift of +1 hour)
Displayed as UTC + 1:
You can see that the Epoch timestamp is 1 hour ahead.
My query is quite simple:
SELECT changeDate as time, (...) FROM tablename. I am not able to use
AT TIME ZONE in SQL Server 2014.
I would very much appreciate any help.
Grafana presumes UTC, you can try with the Dashboard Timezone
But I found a work around, although people will disagree is,
Offset your time with DATEADD(HOUR,-1,Time) AS Time
And Offset your time filter accordingly
Where Time BETWEEN DATEADD( HOUR,+1,$__timeFrom() )
AND DATEADD( HOUR,+1,$__timeTo() )
Takes a bit of messing and then Daylight Savings messes it up again
I knew that Grafana assumes UTC. I just don’t understand why the raw data epoch timestamp is shifted + 1 hour.
@T_GrumpyEngineer thank you for the suggestion, but I was looking for an efficient way of doing it as we have quite a lot of data. I could solve the Daylight Savings calculating each time time-utcTime
This is my query for now, but I would like to solve it in a better way.
DECLARE @offset int = datediff(hour, getutcdate(), getdate());
DATEADD(hour, - @offset, changeDate) as time,
column2 as value
changeDate BETWEEN DATEADD(hour, @offset, CAST($__timeFrom() AS DATETIME2)) AND DATEADD(hour, @offset, CAST($__timeTo() AS DATETIME2))
That’s basically what I did,
Just use the Timezone settings in config and Dashboard/User defaults settings,
You’ll want to upgrade Grafana out of 8.2.1 for best practice,
Grafana 8.3.1, 8.2.7, 8.1.8, and 8.0.7 released with high severity security fix | Grafana Labs