Create bar graph with mysql datasource

Grafana MySQL
1

Hi. I need to create a bar graph using mysql datasource. I have to count the how many occurrences of an event I have daily and display the total of those occurrences per day.

The results in my db look like this:

+--------+---------------------+
| filter | date                |
+--------+---------------------+
|      1 | 2018-08-20 13:10:12 |
|      1 | 2018-08-20 13:09:13 |
|      1 | 2018-08-20 13:08:13 |
|      1 | 2018-08-20 13:07:18 |
|      1 | 2018-08-20 13:03:15 |
|      1 | 2018-08-20 13:02:16 |
|      1 | 2018-08-20 12:59:12 |
|      1 | 2018-08-19 12:54:12 |
|      1 | 2018-08-19 12:48:14 |
|      1 | 2018-08-19 12:47:20 |
|      1 | 2018-08-19 12:46:10 |
|      1 | 2018-08-19 12:42:18 |
|      1 | 2018-08-18 12:41:31 |
|      1 | 2018-08-18 12:40:15 |
|      1 | 2018-08-18 12:39:12 |
|      1 | 2018-08-18 12:38:16 |
|      1 | 2018-08-218 12:37:15 |
+--------+---------------------+

I need to show them in a graph with the following information:
±-------±--------------------+
| date | total |
±-------±--------------------+
| 2018-17-08 | 9 |
| 2018-18-08 | 48 |
| 2018-19-08 | 30 |
| 2018-20-08 | 52 |
±-------±--------------------+

I used the following query to create the graph:

SELECT
  $__timeGroup(date, '1h') as time,
  count(*) as value
FROM my_table
WHERE $__timeFilter(date)
AND filter=1
GROUP BY time

I had to group by 1h as a workaround because I do not find a way to group day (24h), if I use 24h it overlaps the results. And looks like this:

02%20PM

And what I really need is to have the X axis with the date and Y with the total.
Is there a way to do that?

Thanks.

2

I solved using DATE instead of $__timeGroup()

3

I managed to get that using date_format, the point is, grafana still expects “time” as unit_timestamp,
So i got it working this way (mysql data source)

SELECT
unix_timestamp(date_format(date, “%Y-%m-%d 00:00:00”)) as time,
count(*) as value
FROM my_table
WHERE $__timeFilter(date)
AND filter=1
GROUP BY time