How to convert intervals to seconds

Grafana MySQL
, ,
1

Hi,

My dashboard has a interval variable which is used instead of $__interval. What I’m looking for is a way to convert my custom interval from things like “2m” to “120” i.e. the number of seconds. $__interval_ms does this for $__interval but I can do this for my custom interval.

Thanks,
Andreas

1 Like
2

I use this in MySQL, it’s ugly but works. I think that Grafana really should add a conversion macro, which is really a breeze.

IF('$interval' LIKE '%m', '$interval' * 60, IF('$interval' LIKE '%h', '$interval' * 3600, IF('$interval' LIKE '%d', '$interval' * 86400, '$interval')))

Full SQL:

SELECT
  $__unixEpochGroupAlias(timestamp,$interval,0),
  pool_name AS metric,
  sum(diff / IF('$interval' LIKE '%m', '$interval' * 60, IF('$interval' LIKE '%h', '$interval' * 3600, IF('$interval' LIKE '%d', '$interval' * 86400, '$interval')))) AS "hashrate"
FROM shares
WHERE
  $__unixEpochFilter(timestamp)
GROUP BY 1,2
ORDER BY $__unixEpochGroup(timestamp,$interval,0)
1 Like
3

For SQL Server I use:

DECLARE @time_interval_s INT = CASE WHEN '${time_interval}' LIKE '%m' THEN LEFT('${time_interval}',DATALENGTH('${time_interval}')-1) * 60
            WHEN '${time_interval}' LIKE '%h' THEN LEFT('${time_interval}',DATALENGTH('${time_interval}')-1) * 3600
            WHEN '${time_interval}' LIKE '%d' THEN LEFT('${time_interval}',DATALENGTH('${time_interval}')-1) * 86400
            ELSE '${time_interval}'
       END
2 Likes
4

This guy for the win…Saved me a buncha monkeying around.