calculation of 'work time' and 'lunch time' for each day - php/mysql

All employees in/out data entry is fetching in this format:

logtype                            time     Date  
------------------------------------------------------
start_time [come office]           10:30    11-11-2010
end_time [go to lunch]             14:00    11-11-2010 
start_time [come back from lunch]  15:00    11-11-2010开发者_StackOverflow社区
end_time [out from office]         20:00    11-11-2010

Question:

how can I calculate 'start time', 'end time', 'total time including lunch time' and 'total time excluding lunch time'

1. start time
2. end time
3. total time including lunch time
4. total time excluding lunch time

I've thought about it, and I don't see a way of doing this without iterating each item and making the calculations yourself. You could add a "Day" column (e.g. DAYOFMONTH, DAYOFWEEK, or DAYOFYEAR) then begin going over the login/logout times by "time" ascending. The only problem would be "open-ended" times (scenarios where a user didn't punch in/out).

Most time tables I've seen has eliminated this with using (typically) 4 columns.

• Work_Day
• Login_Time
• Logout_Time
• Hours_Worked

Then you can conclude if there was a value missing (one of the time fields would be NULL) and can do a quick query against the table for number of hours worked (or on a weekly bases with a quick column addition using Work_Day as the root of the calculation).

To answer the original question though, the pseudo-code would go something like this:

// $query = mysql_query(....) // <-- populate this
$times = Array('start'=>null,'end'=>null);
$result = Array('start_time'=>null,'end_time'=>null,'total_wo_lunch'=>0,'total_w_lunch'=>0);
while ($log = mysql_fetch_array($query)){
{      
  $_ = $log['date'].' '.$log['time'].':00';
  $_datetime = strtotime($_);

  if (is_null($result['start_time'])) $result['start_time'] = $_datetime;
  else $result['end_time'] = $_datetime;

  if (is_null($times['start'])) $times['start'] = $_datetime;
  else if (is_null($times['end'])) {
    $times['end'] = $_datetime;

    $diff = ($times['end']-$times['start'])/3600; // convert to hours

    $result['total_wo_lunch'] += $diff;

    $times['start'] = null;
    $times['end'] = null;
  }
}
$result['total_w_lunch'] = ($result['end_time']-$result['start_time'])/3600; // convert to hours

print_r($result);

EDITv2

This is an update with code that works. My only issue here is with a bit of rounded on the time card (the worked 8.5 hour day is coming in at 8.53, though I don't see why. Same with the overall 9.5 hour day). other than that it seems to work fine. Let me know if you'd like to see other changes, and my apologies for posting untested code. I was more-or-less going for conceptual.