Convert times to local time

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.)

Well? Is it possible or not?

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?

Did you figure out how to do this?

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