compile visitors list for last 8 days, MySQL

Language: Editor: Layout:

SELECT 
 COUNT( CASE WHEN d between 
        adddate(curdate(), interval -7 day) AND addtime(adddate(curdate(), interval -7 day),'16:00:00')
             THEN 1 END ) dm7,
 COUNT( CASE WHEN d between 
        adddate(curdate(), interval -6 day) AND addtime(adddate(curdate(), interval -6 day),'16:00:00')
             THEN 1 END ) dm6,
 COUNT( CASE WHEN d between 
        adddate(curdate(), interval -5 day) AND addtime(adddate(curdate(), interval -5 day),'16:00:00')
             THEN 1 END ) dm5,
 COUNT( CASE WHEN d between 
        adddate(curdate(), interval -4 day) AND addtime(adddate(curdate(), interval -4 day),'16:00:00')
             THEN 1 END ) dm4,
 COUNT( CASE WHEN d between 
        adddate(curdate(), interval -3 day) AND addtime(adddate(curdate(), interval -3 day),'16:00:00')
             THEN 1 END ) dm3,
 COUNT( CASE WHEN d between 
        adddate(curdate(), interval -2 day) AND addtime(adddate(curdate(), interval -2 day),'16:00:00')
             THEN 1 END ) dm2,
 COUNT( CASE WHEN d between 
        adddate(curdate(), interval -1 day) AND addtime(adddate(curdate(), interval -1 day),'16:00:00')
             THEN 1 END ) dm1,
 COUNT( CASE WHEN d between 
                curdate()                   AND addtime(        curdate()                  ,'16:00:00')
             THEN 1 END ) today
FROM (select     '2017-03-17 15:00:01' d
union all select '2017-03-19 14:00:01'
union all select '2017-03-19 12:00:01'
union all select '2017-03-19 13:00:01'
union all select '2017-03-19 11:00:01'
union all select '2017-03-20 11:20:01'
union all select '2017-03-20 11:30:01'
union all select '2017-03-20 10:40:01'
union all select '2017-03-20 10:23:01' ) dates


absolute service time: 0,31 sec