I’m struggling with displaying a graph from very simple mysql table. There are just three fields: id, date and sensorID - which is equal 1 all the time. Every time an event happens there is a record of it.
date field has datetime type. I would love to display on a graph number of events per day.
I have tried:
SELECT
UNIX_TIMESTAMP(date) as time_sec,
sensorID as value,
sensorID as metric
FROM trigger_tbl
WHERE $__timeFilter(date)
ORDER BY date ASC
where should I put SUM(sensorID) and GROUP BY DATE(date)?
Thanks a lot!
Please use the $__timeGroup macro function. That’s only included in the nightly build right now. If you’re on 4.6.3 or earlier you’ll need to use cast(cast(UNIX_TIMESTAMP(createdAt)/(<seconds>) as signed)*<seconds> as signed) as time_sec, instead.
Taking your example I would use the following query which will return a sum per 24 hours:
SELECT
$__timeGroup(date, '24h') as time_sec,
sum(sensorID) as value,
'Some series/label name' as metric
FROM trigger_tbl
WHERE $__timeFilter(date)
GROUP BY 1
ORDER BY 1
or
SELECT
cast(cast(UNIX_TIMESTAMP(date)/(86400) as signed)*86400 as signed) as time_sec
sum(sensorID) as value,
'Some series/label name' as metric
FROM trigger_tbl
WHERE $__timeFilter(date)
GROUP BY 1
ORDER BY 1
Also make sure that you select Null Value=Null as zero in display tab if you have days where you don’t have any values.
Hi
I use grafana.net online service, when I swapped macro for the cast I got:
Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘) as signed) as signed) as time_sec,
sum(sensorID) as value,
’’ at line 2
any clue what is wrong please?
Thank you so much for your support Markcus!
Hi
Thank you so much for your support, much appreciate.
The code works, I just need to investigate the discrepancy between the grafana and a google chart I created with query:
SELECT DATE(date) as DATE, SUM(sensorID) totalCount FROM my_database.my_table GROUP BY DATE(date)
Very interesting.
Grzegorz
