calculating time attendance in PHP MySQL

Question

I am working with laravel and i have a table with all the users attendances. each row has a flag stating if the user logs in or out. I want to calculate the total working hours of user.

this is my table and sample data

id | user_id |      date     |   timestamp   | status |
 1 |    1    |   2018-05-10  |    17:15:31   |   out  |
 1 |    1    |   2018-05-10  |    13:15:31   |   in   |
 1 |    1    |   2018-05-10  |    12:15:31   |   out  |
 1 |    1    |   2018-05-10  |    08:01:31   |   in   |

I want to calculate the total working hours

$logs = DB::table('attendances as at')
        ->join('infos as in','in.user_id','=','at.user_id')
        ->select('in.name','in.avatar','at.*')
        ->orderBy('id','desc')
        ->where('at.user_id',$id)
        ->get();

 $total_hours = [];

    for($i=0; $i < count($logs)-1; $i++ ){

         if($logs[$i]->status == 'out'){
           $dattime1 = new DateTime($logs[$i]->dt.' '. $logs[$i]->time);
           $dattime2 = new DateTime($logs[$i+1]->dt.' '. $logs[$i+1]->time);

            $total_hours[] = $dattime1 ->diff($dattime2);
         }

    }

 $working_hours = array_sum($total_hours);

Is this the best way to achieve accurate results? Please help. Thanks

Why choose to split date and time when you could have actually used a MySQL DATETIME field? That would have made things easier for you. — apokryfos
– apokryfos, Commented May 10, 2018 at 8:03

Bits Please · Accepted Answer · 2018-05-10 07:54:15Z

1

Can you try like this?

$time1 = "17:15:00";
$time2 = "00:30:00";

$strtotime1 = strtotime($time1);
$strtotime2 = strtotime($time2);
 $o = ($strtotime1) + ($strtotime2);
echo $time = date("h:i:s A T",$o);

Output will be like this:

05:45:00 PM UTC

answered May 10, 2018 at 7:54

Bits Please

9071 gold badge7 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

calculating time attendance in PHP MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related