Convert times to local time

Grafana
1

I am using Grafana 4.4.3 with a MySQL data source on Linux Mint. The timestamp column of one of the tables with data I want to visualize contains time stamps in UTC. I’d like that to be converted to the local time of the viewer. Is there a way of doing that?

I could do the conversion in the MySQL query by using the CONVERT_TZ() function - but it requires the time zone to convert to and I don’t know how to get from Grafana the time zone of the browser. Is there a macro for that?

In the dashboard settings I’ve set the time zone to be “Local browser time” but this doesn’t help, because I don’t know how to get what the offset is. (Well, it does help in the sense that the displayed time is even more wrong if I select the “UTC” setting there.)

2

Well? Is it possible or not?

3

Anyone? Has anything changed on this subject in the new version (4.5.2)? I see that several new macros have been introduced there. Is there one that would let me convert an UTC Unix timestamp into the local timezone of the browser?

4

Did you figure out how to do this?

5

Yes, but you need a relatively more up-to-date version of Grafana than the 4.5.2 version I was using when I wrote about this.

The current version has a bunch of macros than can handle both timestamps in local time and ones in UTC - usually by converting to Unix epoch time and working with that. From the on-line help:

Macros:

  • $__time(column) → UNIX_TIMESTAMP(column) as time_sec
  • $__timeEpoch(column) → UNIX_TIMESTAMP(column) as time_sec
  • $__timeFilter(column) → column BETWEEN FROM_UNIXTIME(1492750877) AND FROM_UNIXTIME(1492750877)
  • $__unixEpochFilter(column) → time_unix_epoch > 1492750877 AND time_unix_epoch < 1492750877
  • $__unixEpochNanoFilter(column) → column >= 1494410783152415214 AND column <= 1494497183142514872
  • $__timeGroup(column,‘5m’[, fillvalue]) → cast(cast(UNIX_TIMESTAMP(column)/(300) as signed)*300 as signed)
    by setting fillvalue grafana will fill in missing values according to the interval
    fillvalue can be either a literal value, NULL or previous; previous will fill in the previous seen value or NULL if none has been seen yet
  • $__timeGroupAlias(column,‘5m’) → cast(cast(UNIX_TIMESTAMP(column)/(300) as signed)*300 as signed) AS “time”
  • $__unixEpochGroup(column,‘5m’) → column DIV 300 * 300
  • $__unixEpochGroupAlias(column,‘5m’) → column DIV 300 * 300 AS “time”

Example of group by and order by with $__timeGroup:
SELECT
$__timeGroupAlias(timestamp_col, ‘1h’),
sum(value_double) as value
FROM yourtable
GROUP BY 1
ORDER BY 1

Or build your own conditionals using these macros which just return the values:

  • $__timeFrom() → FROM_UNIXTIME(1492750877)
  • $__timeTo() → FROM_UNIXTIME(1492750877)
  • $__unixEpochFrom() → 1492750877
  • $__unixEpochTo() → 1492750877
  • $__unixEpochNanoFrom() → 1494410783152415214
  • $__unixEpochNanoTo() → 1494497183142514872