how to sum times in where dates are same in laravel

Question

I am trying to build a time-tracker, in where it has a 'time_tracks' table. An employee can track the time many times in a single day. Now I want to sum the 'total_time' where 'track_date' are same. Would someone help me to find out the expected results, please! Here is my 'trackForToday' function is bellow-

public function trackForToday()
{
    $user_id = Auth::user()->id;

    $last_date = TimeTrack::where('employee_id', $user_id)->select('track_date')->orderBy('track_date', 'DESC')->first();
    $last_work_time = TimeTrack::where('employee_id', $user_id)->where('track_date', $last_date->track_date)->get();

    return view('employee.time-tracker.today');
}

And this is my 'time_tracks' table-

you should consider total_time in seconds, then you could easily use `sum()` to get the total — Sapnesh Naik, Feb 03 '18 at 07:16
I would store date and time as a single entity. If Ann employee works past midnight then you can come to a decision about how to allocate those hours. — Strawberry, Feb 03 '18 at 08:03

Sohel0415 · Accepted Answer · 2018-02-03T08:19:33.663

2

Use sum() collection method and as you are using time type as column type(seems) , you may need to convert it to seconds before summing it.

$total_time = TimeTrack::where('employee_id', $user_id)->where('track_date', $last_date->track_date)->sum(DB::raw("TIME_TO_SEC(total_time)"));

To get data of a specific track date-

$track_date = '2018-03-01';
$total_time = TimeTrack::where('track_date', $track_date)->sum(DB::raw("TIME_TO_SEC(total_time)"));

Last to show it, you need to convert back to time format, see this link for that.

The sum method returns the sum of all items in the collection. If the collection contains nested arrays or objects, you should pass a key to use for determining which values to sum.

edited Feb 03 '18 at 08:19

answered Feb 03 '18 at 07:10

Sohel0415

9,523
21
30

For '2018-03-01' what should I write in the query? – Rashed Hasan Feb 03 '18 at 07:14
is this track date?? – Sohel0415 Feb 03 '18 at 07:15
yes, this is track date. You answered only for last_date , I want also to sum the total_time for any date. – Rashed Hasan Feb 03 '18 at 07:17
My 'track_date' may different date. 'track_date' is inputted by an employee from the front end, so, I can not fix the date. – Rashed Hasan Feb 03 '18 at 07:20
just change the track date to your input date like `$request->input('track_date')` – Sohel0415 Feb 03 '18 at 07:21
Ok. Now I want to show these total time to date wise to the employee.What should I write then? – Rashed Hasan Feb 03 '18 at 07:24
for a specific date, you can do like my answer, for all dates make a froup by query – Sohel0415 Feb 03 '18 at 07:26

how to sum times in where dates are same in laravel

1 Answers1