Error in SQL generation for interval values in days

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:

  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

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?

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

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.

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

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!

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?

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.

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