[SOLVED] Time Question

[savagenoob]

I am trying to create a timeclock system. I have the punch in/out working but now I have to figure out how to calculate the hours and minutes worked between 2 user input dates. My database has timestamps so I am working with a table like

Date/Time Punched

2009-01-15 21:10:10 In

Date/Time Punched

2009-01-15 21:15:34 Out

Date/Time Punched

2009-01-15 21:27:39 In

Date/Time Punched

2009-01-15 22:36:08 Out

Date/Time Punched

2009-01-16 09:28:20 In

and I basically have to calculate the time in hours/min between each in to out within the given date range. I know I probably have to convert each timestamp to minutes, calculate, then reformat to hours/min. Just don't know where to start.

[savagenoob]

Maybe adding a row to the database table that when the user clocks "out" it automatically calculates the minutes since last clock "in" and inserts that to the database as well. Then when the user  searches time worked between 2 dates I can just do a SUM and convert to hours minutes. I can do the code below to calculate the hours and minutes between 2 times, but what if someone clocks in tonight and clocks out tomorrow morning? It would show an error I think...

 

<?php
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 );
}
// Example...
$time1 = "21:10:10";
$time2 = "09:28:20";
if( $diff=@get_time_difference($time2, $time1) )
{
  echo "Hours: " .
       sprintf( '%02d:%02d', $diff['hours'], $diff['minutes'] );
}
else
{
  echo "Hours: Error";
}
// Outputs: Hours: 11:41
?>

[revraz]

Maybe use the MySQL TIMEDIFF?

 

TIMEDIFF(expr1,expr2)

 

TIMEDIFF() returns expr1 – expr2 expressed as a time value. expr1 and expr2 are time or date-and-time expressions, but both must be of the same type.

 

mysql> SELECT TIMEDIFF('2000:01:01 00:00:00',

    ->                '2000:01:01 00:00:00.000001');

        -> '-00:00:00.000001'

mysql> SELECT TIMEDIFF('2008-12-31 23:59:59.000001',

    ->                '2008-12-30 01:01:01.000002');

        -> '46:58:57.999999'

 

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_timediff

[savagenoob]

hm, this would be awesome... but Im a bit confused on how to use this. I read the manual and still confused.

I tried

$result = mysql_query("SELECT TIMEDIFF('2000:01:01 00:00:00', '2000:01:01 00:00:00.000001')");
$final = mysql_fetch_row($result);
echo $final;

and get nothing but "Array"

Sorry for being so Nubish.

[savagenoob]

Getting closer?

<?php
$result = mysql_query("SELECT Time TIMESTAMPDIFF(MINUTE,Time,Now()) FROM timeclock ORDER BY ID DESC LIMIT 1");
while ($myrow = mysql_fetch_assoc($result))
{
	$time = $myrow['Time'];
	echo $time;
}
?>

Still not working but Im trying 

[savagenoob]

Duh...\

<?php
$result = mysql_query("SELECT TIMEDIFF('$time1', '$time2')");
$final = mysql_fetch_row($result);
print_r($final);
?>

But would rather get the one above working. For some reason its not calculating the TIMEDIFF between values in my database.

[revraz]

Replace $time1 and $time2 with your datetime fieldnames for punched in and out.

[savagenoob]

Yeah, I guess I could just run 2 queries to accomplish but thought it could be done in one line... no problem, thanks for the heads up on that mysql function.

[revraz]

You usually can do one query.

[savagenoob]

Im driving myself crazy here...

I just want to run a report that will display the Hours/Min from all the In/Outs in the database within the date range supplied. I can't figure out how to accomplish this and Im just complicating things I think.

[revraz]

Post your table structure and a sample row.

[savagenoob]

IDEmployeeTimeClock

6822009-01-15 21:10:10In

6922009-01-15 22:31:20Out

Hopefully that makes sense.