$__interval_ms Variable not available in MySQL?

Grafana
1

Is it correct that the $__interval_ms varaible can’t be used with MySQL?

I’m using MySQL as a datasource, and just discovered the templating section in the docs.
There are 2 variables described for grouping based on timerange and and pixel width: $__interval and $__interval_ms.

Sadly it seems like only $__interval can be used in the MySQL query. Trying to use the $__interval_ms variable throws an error that looks like the variable couldn’t be found.

I couldn’t figure out how to convert the timestring values from $__interval (1m, 2d, …) to seconds on the fly in the query.
I need a value in seconds for grouping (as UNIX_TIMESTAMP is also in seconds), which would work fine with $__interval_ms:

SELECT
UNIX_TIMESTAMP(TIMESTAMP) as time_sec,
AVG(VALUE) as value,
DEVICE as metric
FROM history
WHERE READING="temperature AND DEVICE=“Thermometer” AND $__timeFilter(TIMESTAMP)
GROUP BY UNIX_TIMESTAMP(TIMESTAMP) DIV ($__interval_ms / 1000)

2

It is available (I think you mus be using a slightly older version of Grafana?) but when testing I discovered a bug in the interpolation code for the MySQL data source which expects all template variables to be strings or arrays of strings. $__interval_ms is a number and gets ignored. Fix will be included in the next release of Grafana -> 4.6.

3

Ok, thank you. :slight_smile:

I just figured out how to use $__interval for my needs:
GROUP BY UNIX_TIMESTAMP(TIMESTAMP) DIV CASE RIGHT($__interval, 1)
WHEN ‘s’ THEN $__interval
WHEN ‘m’ THEN $__interval * 60
WHEN ‘h’ THEN $__interval * 3600
WHEN ‘h’ THEN $__interval * 86400
ELSE $__interval
END

Will go back to above solution once 4.6 is relased.

4

I’m testing this with latest Grafana 6.0.0beta3 still seems to be missing (auto-complete shows __interval but not __interval_ms) too