How to group by time in grafana with Mysql Query

Grafana MySQL
1 
SELECT 
  FLOOR(UNIX_TIMESTAMP(dateField)) as time_sec,
  count(*) as value,
  'No. of Requests' as metric
FROM table
WHERE $__timeFilter(dateField)
GROUP BY dateField
ORDER BY dateField ASC

The above mysql query i want to see in time series visualization , I am able to see the output in seconds (I mean No. of requests per second). But I want the number of requests in a minute , how to use group by clause with $__timeGroup(dateField,‘1m’)?? Can anyone help me how to group by in mysql please?

2

SELECT cast((time_sec/1000) as signed integer) as time_sec,
count(entity_count) as value,
‘Incident Count’ as metric
FROM table
GROUP BY round(time_sec DIV 180)
order by time_sec

This will group by data for 3 minutes .

i tried its working for me but i need to do testing .

3

Here is an example:

SELECT 
  $__timeGroup(atimestamp,'5m') as time_sec,
  avg(afloat) as value, 
  avarchar as metric 
FROM mytable 
GROUP BY 1, avarchar 
ORDER BY 1
1 Like
4

Hi Daniellee,

This $_timeGroup is available in which version of grafana.
I am using grafana 4.6.1

5

Oh sorry, thought it was in 4.6.x. It got added to Grafana in October but didn’t make the 4.6.0 release. It will be available in the next release (probably 5.0 in January).

Here is an example without the timeGroup macro:

SELECT 
  cast(cast(UNIX_TIMESTAMP(atimestamp)/(300) as signed)*300 as signed) as time_sec,
  avg(afloat) as value, 
  avarchar as metric 
FROM mytable
WHERE $__timeFilter(atimestamp)
GROUP BY 1, avarchar 
ORDER BY 1
1 Like