How to add Time series queries with grafana and MySQL?

Grafana Time Series Panel
2

There are lots of examples on the docs page for MySQL: MySQL data source | Grafana documentation

This will show raw time series data (time series is a list of datetime+value pairs):

SELECT
  UNIX_TIMESTAMP(date_insert) as time_sec,
  temperature as value,
  'temperature' as metric
 FROM meteo
WHERE $__timeFilter(date_insert)
ORDER BY date_insert ASC

If you want to group by time (group by hour or day for example), here is one way (there is another example in the docs that uses DIV):

SELECT
  MIN(UNIX_TIMESTAMP(date_insert)) as time_sec,
  avg(temperature) as value
FROM meteo
WHERE $__timeFilter(date_insert)
GROUP BY date_format(date_insert, $interval)
ORDER BY date_format(date_insert, $interval) ASC

$interval is a template variable that allows you to switch from grouping by minute, hour etc. And looks like this:

image
image1261×849 51.6 KB

This is the query in the above screenshot:

select 'minute' AS __text, '%Y%m%d%H%i' as __value union select 'hour' AS __text, '%Y%m%d%H' as __value union select 'day' AS __text, '%Y%m%d' as __value union select 'month' AS __text, '%Y%m' as __value

Hope that these examples are clear. Any tips on how we can keep it short but explain the column names better in the Help section for the MySQL data source?

4 Likes