Grafana receives epoch timestamp 1 hour ahead from SQL Server

Grafana MSSQL
,
#1
  • 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 would very much appreciate any help.

#2

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

1 Like
Exclude field in sql server query from appearing in the legend
Exclude field in sql server query from appearing in the legend
#3

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

1 Like
#4

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))
#5

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

1 Like