CODEWITHSUNDEEP
phplaravellaravel-5eloquent
Dev.W
Dev.W

Reputation: 2370

Laravel Time Difference Sum

I have a basic table which captures all sign in and out of a member of staff.

I am using laravel for my back-end and im struggling how to get the total number of hours on site.

ID | in_time   | out_time | in_date    | out_date
1  | 21:22:49  | 21:46:05 | 2016-01-28 | 2016-01-28
2  | 08:12:12  | 14:12:01 | 2016-01-28 | 2016-01-28

See my query so far

$date1 = '2015-01-28';
$date2 = '2015-01-28';

$attendancehours = DB::table('staff_attendances')->whereBetween('in_date', array($date1, $date2))->where('id', $sID)
        ->get();

How would I output total hours on site for that daterange?

Upvotes: 2

Views: 2448

Answers (1)

Mark Davidson
Mark Davidson

Reputation: 5513

Two ways to go about this are

  1. Use Carbon see the documentation on Difference

    Plenty of examples of this on their website

  2. Assuming your using MySQL use timestampdiff

    SELECT TIMESTAMPDIFF(HOUR,'2003-02-01','2003-05-01 12:05:55');

    You can do this with a RAW query in Laravel something like

    $attendancehours = DB::select(
    DB::RAW('TIMESTAMPDIFF(HOUR,CONCAT(in_date," ",in_time),CONCAT(out_date," ",out_time)'))->
    table('staff_attendances')->
    whereBetween('in_date', array($date1, $date2))->
    where('id', $sID)
    ->get();

Warning: I haven't tested the above but should work.

Upvotes: 1

Related Questions