[HOW TO] Table Configuration - w/ Mysql DataSources

Hello All,

I’m trying to use Table module.

I use a MySQL Datasource to get a Count by day, see my SQL request :

SELECT DISTINCT (
DATE( when )
) AS thedate,
COUNT( * ) AS count
FROM login_old
GROUP BY thedate
ORDER BY count DESC
LIMIT 0 , 30

When i check in my PhpMyadmin Interface i get this array :

See my grafana error with table module :slight_smile:

grafana error

skip the DATE(when) part and use DATETIME or just query the date field directly

Hello Torkel,

Thanks a lot for your reply, i found my good query :

I must type my SELECT DISTINCT in a subrequest with the GROUP BY

SELECT COUNT( * ) AS count
FROM login_old
GROUP BY (
SELECT DISTINCT (DATE( when )) AS thedate)

ORDER BY count DESC
LIMIT 0 , 30

But how i can display the Date for each Resuslt in Table Module ?

Convert your DATE to a DATETIME: CONVERT(thedate, DATETIME)

This will create a datetime like this with no time: 2017-03-01 00:00:00

Thanks Daniellee,

where i have to type this CONVERT, in my subrequest ?
:frowning:

It would work both in the subquery or in the select statement in the outer query.

I haven’t tested this but something like this:

SELECT COUNT( * ) AS count,
CONVERT(thedate, DATETIME) AS time_sec
FROM login_old
GROUP BY (
SELECT DISTINCT (DATE( when )) AS thedate)

ORDER BY count DESC
LIMIT 0 , 30

Hmm doesn’t work :frowning:

Same in the Subquery :

But i found with this solution :

SELECT COUNT( * ) AS count, when AS date
FROM login_old
GROUP BY
(
SELECT DISTINCT (DATE( when )) AS thedate
)
ORDER BY count DESC
LIMIT 0 , 5

I manage with Column style my format Date :slight_smile:
Check my Table :

Thanks a lot for your support daniellee