Perform sum of hours from a query

Question

I am in need of a help in a system that I am developing, it is based on an electronic point system I am almost finishing it, and to complete once I need to perform a calculation of worked hours of a certain period (year/month). The query I already have functional, the only thing that is really left is to take the records of this query and add all to generate the hours worked from that specific period, I will be showing the query below.

SELECT r.cod,r.cod_usuario, r.data, r.entrada, r.intervalo, r.retorno, r.saida, j1.descricao AS justificativa_entrada, j2.descricao AS justificativa_saida,
                        j3.descricao AS justificativa_intervalo, j4.descricao AS justificativa_retorno FROM registro AS r
                        LEFT JOIN justificativa AS j1 ON r.justificativa_entrada = j1.cod
                        LEFT JOIN justificativa AS j2 ON r.justificativa_saida = j2.cod
                        LEFT JOIN justificativa AS j3 ON r.justificativa_intervalo = j3.cod
                        LEFT JOIN justificativa AS j4 ON r.justificativa_retorno = j4.cod
                        WHERE r.cod_usuario = ?  ${monthQuery ? ('AND' + monthQuery) : ''} ${yearQuery ? ('AND' + yearQuery) : ''}           
                        ORDER BY r.data DESC LIMIT 120

This is the query, and its return is this way:

I also have a code that performs the calculation of the hours worked but this is individually, IE, is the calculation of each line of the record. (Date.timeSumFi it’s just a test)

var horasTrabFiltro = Date.timeSumFi((Date.calcHrTrab(re.entrada, re.intervalo, re.retorno, re.saida)));

   Date.calcHrTrab = function (date1, date2, date3, date4) {
        if (date1 == null && date2 == null && date3 == null && date4 == null) {
            return null;
        } else if (date1 != null && date2 != null && date3 != null && date4 != null) {
            dataTrab = Date.timeSum(Date.timeDifference(date1, date2), Date.timeDifference(date3, date4));
            return dataTrab;
        } else if (date1 != null && date2 == null && date3 == null && date4 != null) {
            let date1_ms = new Date(date1).getTime();
            let date4_ms = new Date(date4).getTime();
            let difference_ms = date4_ms - date1_ms;
            difference_ms = difference_ms / 1000;
            let seconds = Math.floor(difference_ms % 60);
            difference_ms = difference_ms / 60;
            let minutes = Math.floor(difference_ms % 60);
            difference_ms = difference_ms / 60;
            let hours = Math.floor(difference_ms);
            return new Date(0, 0, 0, hours, minutes, seconds, 0);
        } else if (date1 != null && date2 != null) {
            let date1_ms = new Date(date1).getTime();
            let date2_ms = new Date(date2).getTime();
            let difference_ms = date2_ms - date1_ms;
            difference_ms = difference_ms / 1000;
            let seconds = Math.floor(difference_ms % 60);
            difference_ms = difference_ms / 60;
            let minutes = Math.floor(difference_ms % 60);
            difference_ms = difference_ms / 60;
            let hours = Math.floor(difference_ms);
            return new Date(0, 0, 0, hours, minutes, seconds, 0);
        }
    }

Sorry for so much information but I believe that the more explained better so that you can help me in my problem, I thank you already.

Answer 1

A simple way to solve it with no library. Good as you already have the function to calculate the hours worked individually is to make a loop in your array coming from the database passing each date individually storing the values in another array, and at the end sum the array of worked hours results.

var horasTrabalhadasDia = [];

//Suponha que resp é seu array vindo da query
resp.forEach(function(re){
    //Suponha que sua função calcHrTrab retorne já o valor das horas trabalhadas
    horasTrabalhadasDia.push(Date.calcHrTrab(re.entrada, re.intervalo, re.retorno, re.saida)); 
});

//Soma de todas as horas trabalhadas durante o mês
var totalHorasMes = horasTrabalhadasDia.reduce((a, p) => a + p); 

Good is a simple example plus the result will be the sum of all hours worked in a given period.