SQL Server and time on X-axis


We started using Grafana last week and I’m trying to get a hold of this software.We are using MS SQL as datasource in Grafana.

We are trying to monitor number of incidents that have been registered in our system at each hour and I am stuck with the date part.

We would like to know the total amount of incidents (displayed on Y-axis) registered today at an hourly rate (displayed on X-axis)

Date format in our database is following: YYYY-MM-DD HH:MM:SS:000

My SQL query is following:

DATEADD(hour,DATEDIFF(hour,0,r.SUBMIT_DATE_UT),0) AS time,
'<name of the type>' as metric,
count(r.ID) as value
from REQUEST r
where r.SUBMIT_DATE_UT between (select DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()), '19000101')) AND
(select DATEADD(DAY, DATEDIFF(DAY, '18991231', GETDATE()), '19000101'))
group by DATEADD(hour,DATEDIFF(hour,0,r.SUBMIT_DATE_UT),0)

When we are using X-axis mode “Series” then it displays the data without time (of course):

However using X-axes mode “Time” then it doesn’t display anything:

Long story short: what is the correct way to format time part of data in Grafana? We would like to how many incidents have been registered in our system every hour (at 08:00, 09:00, 10:00 and so on…)

Thank you!


See the example using __timeGroup macro in the [documentation](http://docs.grafana.org/features/datasources/mssql/#time-series-queries). If you want the graph to only show data for your selected time range of the dashboard, please use the __timeFilter macro.

Note that it’s important for Grafana that you sort the all timestamps in ascending order in your query.