Line Graph X-Axis Shows up as "A"?

Grafana
1

Hi All,

Very novice scripter here trying to create a line graph of counts of a specific value in a column from MSSQL in Grafana.
I presume there is an “A” on the x-axis because I need to declare something in the code and I am sure it is simple. What am I missing? Any help is greatly appreciated.
Visualization is Graph
Format as Table (since the time macros confuse me)

date is column name where the date is stored. Trying to count how many “completed” values per day (date) in the status column.
SQL Query is:

SELECT 
$__timeGroup(date, '1d') as time,
[date], count (*)
FROM [servDep].[dbo].[servDep]

Where [Status] = 'completed'
AND [date] >= DATEADD(day,-30, GETDATE())
Group by date

Thanks much.

A on X_axis

2

Not sure why exactly you get A as the series name but it could be the query alias. Every query has a letter. If you had two queries then the second query would have the alias B.

Your query looks a bit strange. A time series query should return 3 columns at least: a datetime column, a value column and a name column. Your query should look like this:

SELECT
  $__timeGroup(date,'1d') as time,
  'Count' as metric,
  count(*) as value
FROM 
  [servDep].[dbo].[servDep]
WHERE
  $__timeFilter(date) AND
  [Status] = 'completed'
GROUP BY $__timeGroup(date,'1d')
ORDER BY 1

A macro is just helps write sql queries that return time series data as time series is not the same as relational data. You can read about what time series data is here.

The $__timeFilter macro uses the time range in Grafana to build part of the where clause. So this:

$__timeFilter(date)

generates this sql:

date BETWEEN '2020-04-06T18:01:55Z' AND '2020-04-06T18:13:50Z'

The $__timeGroup macro helps you group data per time interval - for example a count per day.

So this:

$__timeGroup(date,'1d') as time,

generates this complicated sql that converts the date to the epoch format and can be used to group by day. (there are 86400 seconds in a day):

FLOOR(DATEDIFF(second, '1970-01-01', createdAt)/86400)*86400 as time

There is more in the documentation about writing queries for MSSQL.

3

Awesome. Much appreciated. Will give that a try.

Thanks.