adding minutes, hours,

[hammerklavier]

Hello,

 

I'm storing times in a mysql table in HH:mm:ss format...although I'm not really using seconds, so I have a bunch of 09:17:00, 07:43:00, 08:01:00 and so on...

 

I wish to add those times and obtain a result again in hour :minute format

 

basically what I'm trying to do is count the hours that a certain employee works during a week.

 

Thanks in advance

[cytech]

Are they clock in clock out?

 

So the employee clocks in a 08:30:00 and then clocks out at 18:30:00 ?

[hammerklavier]

no, well yes.... the employees clocks at say 09:03 and he clocks out at 17:54... here is the script I made:

 

<?php	




   $db = @mysql_pconnect("localhost", "admin", "admin"); 
   if (!$db)
   {
   	echo "Error";
   	exit;
   }

if (mysql_select_db("dbase_com"))
{
   		echo "<font face=Arial, Helvetica, sans-serif size=2>.</font><br><br>";
}		

$starttime = date("H:i");

$s_sql = "select * from xxx_employees where id = '$idname'";
$s_result = mysql_query($s_sql);
if (mysql_num_rows($s_result)) 
{
	$s = mysql_fetch_array($s_result);
}

if ($ssn != $s[ssn])	
{
	echo "It appears as either you have entered a wrong SSN or you are not". $s[firstname];
}
if ($s[timestatus] == 1)
{
		echo "<div align='center'><p><img src='images/xxxfx.jpg'></p></div><br><br>";
	echo "<center><font face=Arial, Helvetica, sans-serif size=2>You already inputed an arrival time, you can't arrive twice at the office ? Who do you think you are ? Agent Smith ??</font><center>";
	echo "<center><font face='arial' size='2'><a href='http://www.xxx.com/qa/home.php'>Back to the Home page</a></b></font></center><br>";

	exit;
} else {

$sql = "insert into employee_times (idname, starttime) values ('$idname', '$starttime')";

$estado = mysql_query($sql, $db);

$sql = "UPDATE xxx_employees SET timestatus = '1' where id = '$idname'";

$estado = mysql_query($sql, $db);        
        
        
	echo "<center><font face='arial' size='2'>Thank you !</b></font></center><br><br><br>"; 
	echo "<center><font face='arial' size='2'><a href='http://www.xxx.com/qa/home.php'>Back to the Home page</a></b></font></center><br>"; 

}


?>

[hammerklavier]

I built one for clocking in and one for clocking out and it's working perfectly... at the end if give the time difference between in and out time:

 

<?php

// the function 

function get_time_difference( $start, $end )
{
    $uts['start']      =    strtotime( $start );
    $uts['end']        =    strtotime( $end );
    if( $uts['start']!==-1 && $uts['end']!==-1 )
    {
        if( $uts['end'] >= $uts['start'] )
        {
            $diff    =    $uts['end'] - $uts['start'];
            if( $days=intval((floor($diff/86400))) )
                $diff = $diff % 86400;
            if( $hours=intval((floor($diff/3600))) )
                $diff = $diff % 3600;
            if( $minutes=intval((floor($diff/60))) )
                $diff = $diff % 60;
            $diff    =    intval( $diff );            
            return( array('days'=>$days, 'hours'=>$hours, 'minutes'=>$minutes, 'seconds'=>$diff) );
        }
        else
        {
            trigger_error( "Ending date/time is earlier than the start date/time", E_USER_WARNING );
        }
    }
    else
    {
        trigger_error( "Invalid date/time data detected", E_USER_WARNING );
    }
    return( false );
}


// submit the endtime

   $db = @mysql_pconnect("localhost", "admin", "admin"); 
   if (!$db)
   {
   	echo "Error";
   	exit;
   }

if (mysql_select_db("xxx_com"))
{
   		echo "<div align='center'><p><img src='images/xxx.jpg'></p></div><br><br>";
}		

$endtime = date("H:i");

$sql = "UPDATE employee_times SET endtime = '$endtime' where idname = '$idname' AND endtime = '00:00:00'";

$estado = mysql_query($sql, $db);

$sql = "UPDATE xxx_employees SET timestatus = '0' where id = '$idname'";

$estado = mysql_query($sql, $db);  

$s_sql = "select * from employee_times where idname = '$idname'";
$s_result = mysql_query($s_sql);
if (mysql_num_rows($s_result)) 
{
	$s = mysql_fetch_array($s_result);
}	


// a START time value



$start = $s[starttime];
// an END time value
$end   = $endtime;

// what is the time difference between $end and $start?
if( $diff=@get_time_difference($start, $end) )
{

echo "Total of hours : minutes worked for today: " .
       sprintf( '%02d:%02d', $diff['hours'], $diff['minutes'] );
}
else
{
  echo "Hours: Error";
}


        
	echo "<center><font face='arial' size='2'><b>Cool addition man !</b></font></center><br><br><br>"; 
	echo "<center><font face='arial' size='2'><b><a href='http://www.xxx.com/qa/home.php'>Add another question</a></b></font></center><br>"; 




?>

[hammerklavier]

and it saves it into a database on a daily basis... now I want those work hours:minute times to be sum

 

for instance 7h 32 min + 8h 04 min + 6h 56 min... and so on........

 

I'm a little fried, and I would like to ask for a bit of help on this matter....

 

thanks in advance

[RussellReal]

ok well.. a word of advice..

 

have a date field also

 

or just in that field

include the date..

 

that way you don't have a major guessing game..

 

unless you already do

 

CLOCK IN

02/31/1990 8:30:01 AM

 

CLOCK OUT

02/31/1990 5:30:01 PM

 

can you show us the structure or just a print_r of a result from the table.. so that we can work out an effective way for you to achieve this?

[hammerklavier]

why do I need the date field ??

 

and how do I add the hours worked ??

[hammerklavier]

I'm sorry I wanted to delete this post.........

 

can anyone help me with my issue of adding times ?

[RussellReal]

because to accurately subtract time or work with time you should convert it to seconds

 

strtotime() will convert 8:30:05 into whatever seconds it was TODAY instead of a WEEK AGO when that was actually recorded..

 

so to keep it straight forward with php you'd include the date aswell..