Inconvergence of consolidated results x by interval

Question

I am making a query for 10 minutes interval between date, the problem is that when I give a between to count all the results it returns a value and when I divide these values by interval and sum at the end it always gives a larger total result, is it a mysql bug?

CONSOLIDATED:

    SELECT 
   sum(case when c.id_queue_call_entry = 3 then 1 else 0 end) as  FILA_700, 
   sum(case when c.id_queue_call_entry = 1 then 1 else 0 end) as  FILA_704, 
   sum(case when c.id_queue_call_entry = 2 then 1 else 0 end) as  FILA_705, 
   sum(case when c.id_queue_call_entry = 4 then 1 else 0 end) as  FILA_708, 
   sum(case when c.id_queue_call_entry = 5 then 1 else 0 end) as  FILA_707, 
   sum(case when c.id_queue_call_entry = 6 then 1 else 0 end) as  FILA_709, 
   COUNT(*) AS TOTAL
FROM
    call_center.call_entry c
WHERE
    datetime_entry_queue BETWEEN '2016-11-03 00:00:00' AND '2016-11-03 23:59:59'  ;

ON A BREAK:

---- mesma consulta
      WHERE
        datetime_entry_queue BETWEEN '2016-11-03 00:00:00' AND '2016-11-03 00:10:00'  ;
---- mesma consulta
      WHERE
            datetime_entry_queue BETWEEN '2016-11-03 00:10:00' AND '2016-11-03 00:20:00'  ;
---- mesma consulta
      WHERE
            datetime_entry_queue BETWEEN '2016-11-03 00:20:00' AND '2016-11-03 00:30:00'  ;

*I’m doing the direct consultation at the bank to test.

total bank image

interval image

Answer 1

You should always make the consultation considering the maximum of the previous period, that is, discounting 1 second from the end:

...WHERE datetime_entry_queue BETWEEN '2016-11-03 00:00:00' AND '2016-11-03 00:09:59';

You can do this using the function SUBTIME:

...WHERE datetime_entry_queue BETWEEN '2016-11-03 00:00:00' AND SUBTIME('2016-11-03 00:10:00', '0 0:0:1.00000'));

Mysql’s SUBTIME function returns a value time or datetime after a certain time interval is removed.

Mysql: SUBTIME Function