[SOLVED] SUM Hours

[inspireddesign]

Hello All,

 

The following is supposed to get the TIMEDIFF then SUM the total hours between the two dates.  It works if the hours and mins are defines but fails if just the mins or secs are defined.

 

For example:  If I have 00:30:00 and 00:00:00 which the TIMEDIFF is .30000 (That's right) with a date range of 10/01/2009 - 10/07/2009 (Date range isn't really relevant to this problem but is needed for my solution) and I then SUM the hours the output is => 3000.00000 not 00.300000 ????

 

Keep in mind that I'll have three or more locations and I'm trying to return the TOTAL HOURS worked for that location for a single employee.  It's unlikely that an employee will only work 1/2 an hour but this is still an error (I feel anyway).

 

My question is the obvious... how can I SUM the TIMEDIFF result using SQL?  I'm sure it can be done (I think)?

 

Here's my statement is it is now:

 

Thanks for any help you can provide me.

 

<?php

													$hrsSql  = "SELECT Emp_Id, Clockout, Clockin, LocationID,    
												    			SUM(TIMEDIFF( Clockout, Clockin )) AS HoursWorked 
																FROM time_clock AS tc
																WHERE tc.Clockin BETWEEN '".$Date1."' AND '".$Date2."' 
																AND tc.LocationID = " . $listLoc->LocationID . "
																AND tc.Emp_Id = " . $listEmps->Id . " 
																GROUP BY tc.Emp_Id";


?>

 

 

 

 

[PFMaBiSmAd]

You cannot do a sum of a timediff because a time value has a mixed number base. You must get the time value into a single number base (seconds is a good choice) before you can do any direct math on it, like a sum. Use TIME_TO_SEC() and SEC_TO_TIME()

[inspireddesign]

That did it.  Thanks for the help.  Here is my revised code for those that might benefit.

 

<?php

													$hrsSql  = "SELECT Emp_Id, Clockout, Clockin, LocationID,    
												    			SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF( Clockout, Clockin )))) AS HoursWorked 
																FROM time_clock AS tc
																WHERE tc.Clockin BETWEEN '".$Date1."' AND '".$Date2."' 
																AND tc.LocationID = " . $listLoc->LocationID . "
																AND tc.Emp_Id = " . $listEmps->Id . " 
																GROUP BY tc.Emp_Id";

?>