Error in SQL generation for interval values in days

Grafana MySQL
,
1

Grafana: 5.0.3
Grafana host: Windows 7 Pro, 64 bit
MySQL server: Maria DB 10.2, Debian Jessie 64 bit

I’ve just added an interval option to a single-line chart based on data from MySQL.

Table spec is:

+-------------+-------------------------------
| Field       | Type
+-------------+-------------------------------
| id          | int(10) unsigned
| source      | enum(<list of source hostnames>)
| target      | enum(<list of ping targets>)
| packet_size | smallint(4)
| start       | datetime
| min         | decimal(5,1)        
| mean        | decimal(5,1)
| max         | decimal(5,1)
| %_lost      | decimal(5,1)
+-------------+--------------------------------

The query spec I’m using in Grafana is:

SELECT
  MAX(UNIX_TIMESTAMP(`start`)) as time_sec,
  AVG(`%_lost`) as value,
  '%_lost' as metric
FROM `ping`
WHERE $__timeFilter(`start`)
GROUP BY $__timeGroup(start, $interval)
ORDER BY $__timeGroup(start, $interval) ASC

Issue
My $interval variable values are: 1h,6h,12h,1d,7d,14d,30d

If I select a value in hours (1h to 12h) for $interval it works fine.

However if I select a value in days (say 7d) then the generation of the query fails with error “error parsing interval 7d”

Is this a known issue, or do I need to log a bug on GitHub?

2

The duration parser does not support d. The workaround is to use hours for those longer intervals too.
I dont think its a bug but you could open a feature request for making the duration parser support longer intervals aswell.

Its not documented on the grafana side but the duration parser only supports ns, us, µs, ms, s, m, h

1 Like
3

Thanks svenklemm.

I guessed that something like this might be the case. However it’s rather odd when the default set of values for a variable of type interval is: 1m,10m,30m,1h,6h,12h,1d,7d,14d,30d. So 40% of the default values don’t work.

My source data was collected at hourly intervals and spans roughly a decade, so intervals from 1h up to a month would be appropriate.

I see i can work around this using intervals in hours, but 30d in hours isn’t a number that’s immediately recognisable.

I’ll look into logging a bug/enhancement request.

Thanks for your help.

4

If you dont wanna show those numbers you could use a query variable and get human readable presention for your values.

SELECT '1 week' as __text, '168h'  AS __value UNION SELECT '1 day' as __text, '24h' AS __value

Or create a table with your values.
But I agree it would be nice if ‘d’ and ‘w’ would be supported.

1 Like
5

Hmm, I didn’t know you could map visible choices in the pulldown to internal values like that in the Grafana interface. I’ll give it a try, thanks!

6

I’m having the same issue. I can map new options and make that work, but what about the “auto” option, it selects 1d automatically, if I convert them all to hours will auto also stop using days and weeks?

7

Also, is this only an issue with SQL driver? Seems like you could just do a conversion inside the ParseDuration function and make it work out of the box.

9

Is this issue fixed?
I’m also getting similar error like error parsing interval ‘1M’
Is this bug reported?