MySQL - How to count the Date and Time fields of each time slot?

Considering that each day has 48 bands of 30 minutes (0 - 47), the records can be grouped by strip number as follows:

select concat(sec_to_time(franja_id * 1800), ' - ', sec_to_time((franja_id+1) * 1800 - 1)) as franja_horario,
       count(franja_id) as conteo
  from (select time_to_sec(
                   timediff(fecha_contacto, cast(date(fecha_contacto) as datetime))
               ) div 1800 as franja_id
          from diferidas) t
 group by franja_id
 order by franja_id;

Demo

Note that the strip number is obtained by obtaining the number of seconds between midnight and the time of fecha_contacto , and then dividing by the number of seconds in 30 minutes (1800 seconds).

Now, your second problem needs to be solved: get the stripes with zero account. For this, you must establish the list of strips you want and define them as a table to which you can make a left join.

One way to do this is by creating a derived table with all the strip numbers that interest you. As you would like all stripes from 08:00:00 - 08:29:59 to 18:00:00 - 18:29:59 , this equals the strips 16 to 36 :

select concat(sec_to_time(f.franja_id * 1800), ' - ', sec_to_time((f.franja_id+1) * 1800 - 1)) as franja_horario,
       count(t.franja_id) as conteo
  from (select 16 as franja_id
        union all select 17
        union all select 18
        union all select 19
        union all select 20
        union all select 21
        union all select 22
        union all select 23
        union all select 24
        union all select 25
        union all select 26
        union all select 27
        union all select 28
        union all select 29
        union all select 30
        union all select 31
        union all select 32
        union all select 33
        union all select 34
        union all select 35
        union all select 36) f
  left join (select time_to_sec(
                        timediff(fecha_contacto, cast(date(fecha_contacto) as datetime))
                    ) div 1800 as franja_id
               from diferidas) t
    on t.franja_id = f.franja_id
 group by f.franja_id
 order by f.franja_id;

Demo

Or, if you prefer that your query is not contaminated with that fey derived table, you can create a table dedicated to that purpose with the necessary data:

create table Franjas (
  franja_id int not null primary key    
);

insert into Franjas (franja_id)
values (16), (17), (18), (19), (20), (21), (22), (23), (24), (25),
(26), (27), (28), (29), (30), (31), (32), (33), (34), (35), (36);

In this way, the query is more compact:

select concat(sec_to_time(f.franja_id * 1800), ' - ', sec_to_time((f.franja_id+1) * 1800 - 1)) as franja_horario,
       count(t.franja_id) as conteo
  from Franjas f
  left join (select time_to_sec(
                        timediff(fecha_contacto, cast(date(fecha_contacto) as datetime))
                    ) div 1800 as franja_id
               from diferidas) t
    on t.franja_id = f.franja_id
 group by f.franja_id
 order by f.franja_id;

Demo

I repeat the answer in your other question, which they closed in duplicate.

It does not help to do left join of the table with itself. You need to make a "dummy" table that contains all stripes in hard:

SELECT '08:30 - 08:59' as franja UNION ALL
SELECT '09:30 - 09:59' UNION ALL
SELECT '10:30 - 10:59' UNION ALL
SELECT '11:30 - 11:59' UNION ALL
SELECT '12:30 - 12:59' UNION ALL
SELECT '13:30 - 13:59' UNION ALL
SELECT '14:30 - 14:59' UNION ALL
SELECT '15:30 - 15:59' UNION ALL
SELECT '16:30 - 16:59' UNION ALL
SELECT '17:30 - 17:59' UNION ALL
SELECT '18:30 - 18:59' UNION ALL
SELECT '19:30 - 19:59' 

to this table you make a LEFT JOIN with the table of contacts replacing the nulls with zeros:

SELECT franjas.franja, IFNULL(conteo,0) as conteo
FROM franjas LEFT JOIN contactos ON franjas.franja=contactos.franja;

I gave you a rextester example with my solution proposal: link

Good morning!

To begin with, I think that what you need is the HOUR() of MySQL function, which gives you the time. Even if you want to do it in half-hour slots you can use the MINUTE() function to get the minutes of a timestamp.

Once with this you can make the query by adding the records that meet the IFs, so you will have the sum per time slot.

For example, for hours of whole hours you would have something like this:

SELECT 
SUM(if(HOUR(T.TIME_CONTACT) = 0, 1, 0)) AS FRANJA_0,
SUM(if(HOUR(T.TIME_CONTACT) = 1, 1, 0)) AS FRANJA_1,
SUM(if(HOUR(T.TIME_CONTACT) = 2, 1, 0)) AS FRANJA_2,
SUM(if(HOUR(T.TIME_CONTACT) = 3, 1, 0)) AS FRANJA_3,
SUM(if(HOUR(T.TIME_CONTACT) = 4, 1, 0)) AS FRANJA_4,
SUM(if(HOUR(T.TIME_CONTACT) = 5, 1, 0)) AS FRANJA_5,
SUM(if(HOUR(T.TIME_CONTACT) = 6, 1, 0)) AS FRANJA_6,
SUM(if(HOUR(T.TIME_CONTACT) = 7, 1, 0)) AS FRANJA_7,
SUM(if(HOUR(T.TIME_CONTACT) = 8, 1, 0)) AS FRANJA_8,
SUM(if(HOUR(T.TIME_CONTACT) = 9, 1, 0)) AS FRANJA_9,
SUM(if(HOUR(T.TIME_CONTACT) = 10, 1, 0)) AS FRANJA_10,
SUM(if(HOUR(T.TIME_CONTACT) = 11, 1, 0)) AS FRANJA_11,
SUM(if(HOUR(T.TIME_CONTACT) = 12, 1, 0)) AS FRANJA_12,
SUM(if(HOUR(T.TIME_CONTACT) = 13, 1, 0)) AS FRANJA_13,
SUM(if(HOUR(T.TIME_CONTACT) = 14, 1, 0)) AS FRANJA_14,
SUM(if(HOUR(T.TIME_CONTACT) = 15, 1, 0)) AS FRANJA_15,
SUM(if(HOUR(T.TIME_CONTACT) = 16, 1, 0)) AS FRANJA_16,
SUM(if(HOUR(T.TIME_CONTACT) = 17, 1, 0)) AS FRANJA_17,
SUM(if(HOUR(T.TIME_CONTACT) = 18, 1, 0)) AS FRANJA_18,
SUM(if(HOUR(T.TIME_CONTACT) = 19, 1, 0)) AS FRANJA_19,
SUM(if(HOUR(T.TIME_CONTACT) = 20, 1, 0)) AS FRANJA_20,
SUM(if(HOUR(T.TIME_CONTACT) = 21, 1, 0)) AS FRANJA_21,
SUM(if(HOUR(T.TIME_CONTACT) = 22, 1, 0)) AS FRANJA_22,
SUM(if(HOUR(T.TIME_CONTACT) = 23, 1, 0)) AS FRANJA_23
FROM DIFERIDAS T;

Well and to finish curling the curl this would be the query to see the calls by half hour strips from 8.00 to 18.00:

SELECT 
SUM(if(HOUR(T.TIME_CONTACT) = 8 and MINUTE(T.TIME_CONTACT) >= 0 and MINUTE(T.TIME_CONTACT) <= 29, 1, 0)) AS FRANJA_8_00,
SUM(if(HOUR(T.TIME_CONTACT) = 8 and MINUTE(T.TIME_CONTACT) >= 30 and MINUTE(T.TIME_CONTACT) <= 59, 1, 0)) AS FRANJA_8_30,
SUM(if(HOUR(T.TIME_CONTACT) = 9 and MINUTE(T.TIME_CONTACT) >= 0 and MINUTE(T.TIME_CONTACT) <= 29, 1, 0)) AS FRANJA_9_00,
SUM(if(HOUR(T.TIME_CONTACT) = 9 and MINUTE(T.TIME_CONTACT) >= 30 and MINUTE(T.TIME_CONTACT) <= 59, 1, 0)) AS FRANJA_9_30,
SUM(if(HOUR(T.TIME_CONTACT) = 10 and MINUTE(T.TIME_CONTACT) >= 0 and MINUTE(T.TIME_CONTACT) <= 29, 1, 0)) AS FRANJA_10_00,
SUM(if(HOUR(T.TIME_CONTACT) = 10 and MINUTE(T.TIME_CONTACT) >= 30 and MINUTE(T.TIME_CONTACT) <= 59, 1, 0)) AS FRANJA_10_30,
SUM(if(HOUR(T.TIME_CONTACT) = 11 and MINUTE(T.TIME_CONTACT) >= 0 and MINUTE(T.TIME_CONTACT) <= 29, 1, 0)) AS FRANJA_11_00,
SUM(if(HOUR(T.TIME_CONTACT) = 11 and MINUTE(T.TIME_CONTACT) >= 30 and MINUTE(T.TIME_CONTACT) <= 59, 1, 0)) AS FRANJA_11_30,
SUM(if(HOUR(T.TIME_CONTACT) = 12 and MINUTE(T.TIME_CONTACT) >= 0 and MINUTE(T.TIME_CONTACT) <= 29, 1, 0)) AS FRANJA_12_00,
SUM(if(HOUR(T.TIME_CONTACT) = 12 and MINUTE(T.TIME_CONTACT) >= 30 and MINUTE(T.TIME_CONTACT) <= 59, 1, 0)) AS FRANJA_12_30,
SUM(if(HOUR(T.TIME_CONTACT) = 13 and MINUTE(T.TIME_CONTACT) >= 0 and MINUTE(T.TIME_CONTACT) <= 29, 1, 0)) AS FRANJA_13_00,
SUM(if(HOUR(T.TIME_CONTACT) = 13 and MINUTE(T.TIME_CONTACT) >= 30 and MINUTE(T.TIME_CONTACT) <= 59, 1, 0)) AS FRANJA_13_30,
SUM(if(HOUR(T.TIME_CONTACT) = 14 and MINUTE(T.TIME_CONTACT) >= 0 and MINUTE(T.TIME_CONTACT) <= 29, 1, 0)) AS FRANJA_14_00,
SUM(if(HOUR(T.TIME_CONTACT) = 14 and MINUTE(T.TIME_CONTACT) >= 30 and MINUTE(T.TIME_CONTACT) <= 59, 1, 0)) AS FRANJA_14_30,
SUM(if(HOUR(T.TIME_CONTACT) = 15 and MINUTE(T.TIME_CONTACT) >= 0 and MINUTE(T.TIME_CONTACT) <= 29, 1, 0)) AS FRANJA_15_00,
SUM(if(HOUR(T.TIME_CONTACT) = 15 and MINUTE(T.TIME_CONTACT) >= 30 and MINUTE(T.TIME_CONTACT) <= 59, 1, 0)) AS FRANJA_15_30,
SUM(if(HOUR(T.TIME_CONTACT) = 16 and MINUTE(T.TIME_CONTACT) >= 0 and MINUTE(T.TIME_CONTACT) <= 29, 1, 0)) AS FRANJA_16_00,
SUM(if(HOUR(T.TIME_CONTACT) = 16 and MINUTE(T.TIME_CONTACT) >= 30 and MINUTE(T.TIME_CONTACT) <= 59, 1, 0)) AS FRANJA_16_30,
SUM(if(HOUR(T.TIME_CONTACT) = 17 and MINUTE(T.TIME_CONTACT) >= 0 and MINUTE(T.TIME_CONTACT) <= 29, 1, 0)) AS FRANJA_17_00,
SUM(if(HOUR(T.TIME_CONTACT) = 17 and MINUTE(T.TIME_CONTACT) >= 30 and MINUTE(T.TIME_CONTACT) <= 59, 1, 0)) AS FRANJA_17_30
FROM DIFERIDAS T;