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: 2022-01-27 12:47:09
Column is changeDate datetime2(7) . I’m using DBeaver configured in CET (I am in UTC+1 timezone).
[DBeaver]
Cell value: 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.
[Grafana]
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: 2022-01-27 14:47:09
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 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());
SELECT
DATEADD(hour, - @offset, changeDate) as time,
column2 as value
FROM
tablename
WHERE
changeDate BETWEEN DATEADD(hour, @offset, CAST($__timeFrom() AS DATETIME2)) AND DATEADD(hour, @offset, CAST($__timeTo() AS DATETIME2))