TIMEDIFF with period exception (Business hours)

Asked

Viewed 106 times

0

Hello, I have an SQL query that returns the time difference between 2 dates, but I need to calculate this difference by adding a period (08:00 - 18:00) for example:

Start date: 2019-10-18 15:00:00

End Date: 2019-10-19 09:00:00

Expected result: 04:00:00

SELECT TIMEDIFF(tb_paradas.data_fechamento,tb_paradas.data_abertura) AS difSla FROM tb_paradas

How can I add the business time exception to my query? It would be easier for me to handle this via sql query or in php?

php mysql

  • Adding or considering only business hours which, by the way, may vary.

  • considering only the schedule 08:00 - 18:00

  • Considering that the interval can be several days so you need to consider the initial time and the final day by day and keep accumulating. Also need to disregard weekends and holidays.

  • by complexity I have not considered including holidays and weekends for the time being.. and rather treat it in php so the user can not register in these files

1 answer

1


Solved via php. If someone with the same problem follows me code already considering Saturday/Sunday and array for holiday.. Detail in the code that for Saturday the period is different that was also adapted in the code (time from 07:30 - 11:00)

function convertHoras($horasInteiras) {

    // Define o formato de saida
    $formato = '%02d:%02d';
    // Converte para minutos
    $minutos = $horasInteiras * 60;
    // Converte para o formato hora
    $horas = floor($minutos / 60);
    $minutos = ($minutos % 60);
    // Retorna o valor
    return sprintf($formato, $horas, $minutos);
}                 

function isFeriado($dia){

    $arrayFeriados = [
        "2018-08-10"
    ];

    if(in_array($dia,$arrayFeriados)){
        return true;
    }
    return false;
}

function getHoras($dataIni,$dataFim,$pausa){

 $datatime1 = new DateTime($dataIni);
 $datatime2 = new DateTime($dataFim);
 $intervaloEmMinuto = new DateInterval('PT1M');
 $periodo = new DatePeriod($datatime1, $intervaloEmMinuto, $datatime2);
 $minutos = 0;


foreach ($periodo as $data) {

    $dia = $data->format("Y-m-d");  

   if(substr($data->format("D"), 0, 2) == 'Su' || isFeriado($dia)){

    continue;

  }

   /* @var $data \DateTime */

   $dataEmMinuto = clone $data;

    // Horário de entrada - Seg/Sabado
   $inicioDoPrimeiroTurno = clone $dataEmMinuto->setTime(7, 29, 0);

   // Horário de saída
   if(substr($data->format("D"), 0, 2) == 'Sa') { 
   // Se sábado = 11:00
    $fimDoPrimeiroTurno = clone $dataEmMinuto->setTime(11, 00, 0);
   } else {
   // Dias da semana = 17:00
    $fimDoPrimeiroTurno = clone $dataEmMinuto->setTime(17, 00, 0);   
   }

   if (($inicioDoPrimeiroTurno < $data && $data < $fimDoPrimeiroTurno)) {

     $minutos++;

   }
}

 $intervalo = new DateInterval("PT{$minutos}M");
 $data = new DateTime();
 $dataAtual = clone $data;
 $data->add($intervalo);
 $data->sub(new DateInterval("PT".abs($pausa)."M"));
 $d = $dataAtual->diff($data);
 $seconds = $d->s + ($d->i * 60) + ($d->h * 3600) + ($d->d * 86400) + ($d->m * 2592000); // and so on

$hour= $seconds/3600;

return $hour;

}

// $data_abertura e $data_fechamento = são os valores que vem pelo banco de dados
echo $slaFinal = convertHoras(getHoras($data_abertura,$data_fechamento,false));

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.