The Start and Stop period could be severals days/weeks.
I would like to represent the number of disconnection on a chart.
With no zoom, I might get the number of disconnection for each day, by zooming, I might get the number of disconnection for each hours, more each minutes and more each seconds. 0 should be displayed when there is no events.
In the same way as:
Before, for a solution which is not dynamic, I've used a mysql query to get the count for every minutes:
select DATE_FORMAT(time, '%Y-%m-%d %H:%i') as date, count(*) as Count from db WHERE event = "disconnected" group by date
But the result is:
2019-09-18 00:36 4 2019-09-18 18:14 2 2019-10-02 11:43 2 2019-10-02 11:44 1
I would need to get every minutes with 0 when there is not events to get a correct barchart.
Right now, I have this display:
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
ALTER TABLE `db` ADD INDEX `db_idx_event` (`event`);
SELECT DATE_FORMAT(db.time, '%Y-%m-%d %H:%i') AS date, count(*) AS Count FROM db WHERE db.event = 'disconnected' GROUP BY date ORDER BY NULL