Datasource is in local time, hilarity ensues…

Grafana
#1

[Grafana 5.1 on Ubuntu 18.04, data from MS SQL 2008]

Hi, have data in an old, dusty MS SQL server, where datetimes are recorded in local time, which is Pacific Time.

No, I’m not able to change this. :-/

Unfortunately Grafana is subtracting 7 hours from all our data because it thinks it is in UTC. Changing Browser time to UTC at Grafana does the opposite of what I need and doubles the problem.

I’ve tried modifying the datetimes with SQL queries, but that conflicts with the time filter, so can’t zoom in further than 12 hours:

SELECT
  dateadd(hour, datediff(hour,getdate(),getutcdate()), Time_Stamp) as time
FROM
  Data_Table
WHERE
  $__timeFilter(Time_Stamp)

Not sure what to do. Servers are in Pacific Time. Grafana server hardware is in UTC but OS set to Pacific Time. Open to changing Ubuntu server to UTC, but not sure that will make a difference.

Need Grafana to leave the times from this datasource alone. How do I do that?

1 Like
#2

Grafana requires that the data is in UTC m, any chance you can change time in query?

#3

Thanks, yes, am changing the times, though not very efficient.

The biggest problem however is that the time filter window macro is wrong, it can’t be changed can it? That means the first 7 hours of data are missing, and if I zoom in to last 6 hours or less, there is no data at all :-(.

Perhaps this feature is not so hard to implement, believe I need a “leave times alone” button, haha.

#4

We’ve dealt with a similar situation with Postgres data. If in your query you convert all timestamps (including the one used for the time filter) to UTC, it should work fine. Looks like more recent version of MS SQL Server have an “AT TIME ZONE” function which should accomplish that in a relatively elegant way.

Two other things to consider:

  • Instead of just applying $__timeFilter(), you could expend the filter and explicitly compare against UTC timestamps, e.g.
WHERE
  Time_Stamp AT TIME ZONE 'UTC' > $__timeFrom() AT TIME ZONE 'UTC' AND
  Time_Stamp AT TIME ZONE 'UTC' <= $__timeTo() AT TIME ZONE 'UTC'
  • In Postgres we had to cast some of the timestamps to ::timestamp type in order to explicitly remove the associated timezone offset after doing ts AT TIME ZONE 'UTC'. Without doing that, they remain of type timestamptz, which seems to confuse things.
#5

Thanks, though MS SS 2008 doesn’t support that syntax, I was able to get around the time window issue with this monstrosity:

-- Fix local time :-(
DECLARE @offset int        = datediff(hour, getdate(), getutcdate()) -- About X hrs
DECLARE @fromtime datetime = dateadd(hour, -@offset, $__timeFrom())
DECLARE @to__time datetime = dateadd(hour, -@offset, $__timeTo())

SELECT
  dateadd(hour, @offset, Time_Stamp) as time,  -- << move timestamp to UTC 
  Field1,
  Field2
FROM
  Table1
WHERE
  Time_Stamp >= @fromtime AND Time_Stamp <= @to__time
ORDER BY
  Time_Stamp ASC

Any tips for my SQL appreciated, it’s a second language to me.

As can be seen, a checkbox in Grafana that said, “X Leave times alone,” would be great. :-D.

1 Like
#6

…and in MySQL this seems to work:

SELECT
   CONVERT_TZ(time, 'CET', 'UTC') AS 'time',
   therm_status,
   t_pi,
   t_therm,
   therm_heatup,
   boil_status
FROM 
   home_log_15min
WHERE
   $__timeFilter(time)
ORDER BY time

the mysql.time_zone_name might need to get populated:
sudo mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u admin mysql -p

#7

There is issue in grafana where it checks time as UTC time but when in interval comparison or group by it checks time as local and only converting interval time to UNIX .

Solution - You should add one more column to table which should contains UNIX equivalent time of your timestamp and pass that in time column and everything will work.

Eg.

Local Browser Time - 2020-05-31 17:21:05 (It is UTC + 5:30)
UNIX_TIMESTAMP - 1590945665