Inconvergence of consolidated results x by interval

0

I am doing a query for a 10-minute interval between dates, the problem is that when I give an between to count all the results it returns a value and when I divide those values by interval and somo at the end it always gives a result total greater, 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'  ;

WITH INTERVAL:

---- 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 query on the bench to test.

total bank image

image interval

    
asked by anonymous 21.11.2016 / 19:43

1 answer

2

You should always consult the maximum of the previous period, ie, deducting 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 SUBTIME function:

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

The MySQL SUBTIME function returns a value of time or datetime after a certain time interval is removed.

MySQL: SUBTIME Function

    
21.11.2016 / 19:45