Difference of exact hours between sumHoras and hrsDia, Excel PHP

To subtract the dates / hours try the following:

<?php

$fechaA = '2018-01-01 01:11:01';
$fechaB = '2018-01-01 02:22:00';

$diff = gmdate("H:i:s", strtotime($fechaB)-strtotime($fechaA));

Next, simply use that variable to store the result in the excel cell.

Demo

NOTE:

If you only have time (without dates) you can achieve the same by adding a date in front:

<?php

$timeA = '01:11:01';
$timeB = '02:22:00';

$fechaA = "1970-01-01 $timeA";
$fechaB = "1970-01-01 $timeB";


# Formato HH:MM:SS
$diff = gmdate("H:i:s", strtotime($fechaB)-strtotime($fechaA));

Demo

The function strtotime what it does is calculate the number of seconds that have passed since January 1, 1970. Then, subtract the number of seconds of the two dates / times and give it the format that interests you with gmdate

I do not understand very well how% of% of% you get Hours A , because I do not see any alias in the query that appears in the example, but I think the problem is not there. In fact, I think there is no problem, I explain:

According to what you say towards the end:

  

Then I send HoursA as sumHora to an Excel column and Hours B   like hrsDia, here I take out the difference divided by 3600, the value   absolute, and round to 2 decimals. For which I get the   "Difference" specified in Example A ie 4.98

     

(...)

     

However, the correct result should be 4.58 and not 4.98 since if   multiply 0.58 by 60 minutes are obtained.

I have made the same calculation ( Hours A - Hours B ) and I think the difference is 4 hours and 59 minutes. You indicate that the difference is 4.98 for you, but that it should be 4.58.

The problem is that you are misinterpreting it, because the result is fine. If you multiply the 0.98 from the 4.98 by 60 (conversion of fraction of an hour by minute in an hour), ie: 0.98 * 60, you get 58.8 minutes (~ 59 minutes), that surely if you take the hidden decimals of 0.98, you will get the 59 minutes.

To make the result more understandable, I think of several ways:

• That the title of the cell is "Difference (hours)." In this way you indicate the unit and if someone wants to take the minutes, you can multiply the decimal part by 60.

• Transform the content of numeric to date, instead of having the format 4.98, which is 4:59. Something like (I have not tried it):

   $diferencia = round($suma=abs($suma=strtotime($datos['sumaHoras'])-strtotime($row['hrsDia']))/3600,2))
   ->setCellValue('L'. $i,(($varSumaHoras=='')?'':sprintf('%02d:%02d', (int) $diferencia, fmod($diferencia, 1) * 60))
  

• Divide the result into two cells: The difference consists of the cells "Hours" and "Minutes". Something like (I have not tried it):

   // calcular la diferencia
   $diferencia = round($suma=abs($suma=strtotime($datos['sumaHoras'])-strtotime($row['hrsDia']))/3600,2)
   // separar la diferencia por el punto para obtener la unidad y el decimal en valores separados
   list($whole, $decimal) = explode('.', $diferencia);
   // escribir la parte completa en una celda
   ->setCellValue('L'. $i,(($varSumaHoras=='')?'':whole))
   // escribir la parte decimal en otra celda, pero multiplicandola por 60
   ->setCellValue('M'. $i,(($varSumaHoras=='')?'':decimal*60))
  

I hope to have explained myself well. Anything, you tell me.

Regards,

Unai

When viewing the following query:

$qry = "select 
                CONVERT(VARCHAR(20),fechaSalida,108) AS fechaSalida,
                CONVERT(VARCHAR(20),fechaEntrada,108) AS fechaEntrada,
                convert(varchar(10), ((DATEDIFF(SECOND, fechaEntrada, fechaSalida)%86400)/3600)) + ':'+                  
                convert(varchar(10), (((DATEDIFF(SECOND, fechaEntrada, fechaSalida)%86400)%3600)/60))  as sumaHoras

            from tblasistencia where idclave = '".$empno."' and 
                fechaEntrada BETWEEN CONVERT(DATETIME, '".$fechainicio." 00:00:00',103) and 
                CONVERT(DATETIME, '".$fechainicio." 23:59:59',103)";

One has the impression that you are drowning in a glass of water at first.

I will leave the following criteria for your consideration:

Write an SQL query whose results are destined for a programming language (PHP or other), in where you repeat again and again columns as fechaSalida , fechaEntrada applying tasks as conversions, calculations, formatting, etc, etc, what it does is unnecessarily recharging the database manager.

My proposal is very simple:

• Obtain from the database the data you need, without so many repetitions, calculations, conversions
• Treat data obtained by programming

If we apply this, your SQL query would look like this:

$qry = "SELECT 
                fechaSalida,
                fechaEntrada,
        FROM tblasistencia 
        WHERE  ....
       ";

What you do here is simply bring the data as is to PHP and from there you can do the calculations you want.

PHP has the powerful class DateTime which allows you to create a true date object from a well-represented date / time. With that class you just have to create two date objects and from them to do any type of operation, calculation, format, etc.

Suppose that fechaEntrada and fechaSalida show these values:

$fechaEntrada="2018-10-03 06:51:27";
$fechaSalida ="2018-10-03 19:51:00";

We create two objects from them:

$fechaIn=new DateTime($fechaEntrada);
$fechaOut=new DateTime($fechaSalida);

Well, both $fechaIn and $fechaOut are two objects with all of the law that represent your departure / arrival data.

If you want to see one of them inside:

var_dump($fechaIn);

Exit:

object(DateTime)#1 (3) {
  ["date"]=>
  string(26) "2018-10-03 06:51:27.000000"
  ["timezone_type"]=>
  int(3)
  ["timezone"]=>
  string(13) "Europe/Berlin"
}

And what do I do with two objects, what does it do for me?

That from them you can do how many calculations and representations you want , expressed in hours, minutes, seconds, letters, days, weeks, quarters ...

Let's see some examples of representation (note that we always use the same object, not that we create the object again and again as it is done in your SQL query):

echo $fechaIn->format('Y-m-d H:i:s').PHP_EOL;
echo $fechaIn->format('Y-m-d').PHP_EOL;
echo $fechaIn->format('H:i:s').PHP_EOL;
echo $fechaIn->format('g:i A').PHP_EOL;

Exit:

2018-10-03 06:51:27
2018-10-03
06:51:27
6:51 AM

It's not just format, we can also do calculations . For example, I could not decipher the calculation you want to do here:

convert(varchar(10), ((DATEDIFF(SECOND, fechaEntrada, fechaSalida)%86400)/3600)) + ':'+                  
                convert(varchar(10), (((DATEDIFF(SECOND, fechaEntrada, fechaSalida)%86400)%3600)/60))  as sumaHoras

but whatever the calculation you can do from your two objects and at the same time express it in a given format.

Suppose you want to know how many hours have passed between entry and exit:

$timeWorking = $fechaIn->diff($fechaOut);

What produces $timeWorking is not just any variable, it is also an object with a quantity of information that you hallucinate. Let's see it inside:

object(DateInterval)#3 (15) {
  ["y"]=>
  int(0)
  ["m"]=>
  int(0)
  ["d"]=>
  int(0)
  ["h"]=>
  int(12)
  ["i"]=>
  int(59)
  ["s"]=>
  int(33)
  ["weekday"]=>
  int(0)
  ["weekday_behavior"]=>
  int(0)
  ["first_last_day_of"]=>
  int(0)
  ["invert"]=>
  int(0)
  ["days"]=>
  int(0)
  ["special_type"]=>
  int(0)
  ["special_amount"]=>
  int(0)
  ["have_weekday_relative"]=>
  int(0)
  ["have_special_relative"]=>
  int(0)
}

Among the information that has $timeWorking we have the number of years y , months m , days d , hours h .... that have elapsed between the two original objects and many more things .

And the thing does not end there, if you want to present that information in a certain way, you can use another method applied to that same object . For example:

echo $timeWorking->format('%H:%I:%S');

Exit:

12:59:33

Conclusion

  

I'm sorry if I've gone a bit longer in the explanation.

     

The answer was aimed at showing the power of the objects .   In cases like these it is convenient to charge as little as possible to the   database, bring our information in the simplest way   possible and then draw on the possibilities of language (in this   case class DateTime of PHP) to manipulate that information.

     

I hope the answer is useful. What I propose is to focus the   solving the problem from another point, simplifying the code.

NOTE ON SECURITY:

The SQL query that you are launching from PHP is highly vulnerable to SQL injection attacks. Consider the use of queries prepared to neutralize that possibility of attack that could have unfortunate consequences on your data or on your system itself.