calculating total hours from times laravel sql

Use Carbon

$time = new Carbon($shift_start_time);
$shift_end_time =new Carbon($shift_end_time);
$time->diffForHumans($shift_end_time);

row sql: SELECT time_sheet_id, sum( TIMEDIFF( shift_end_time, shift_start_time ) ) FROM yourtable

@spyworld how can i get the sum for specific timesheet_id using this?

@devtraining : this give result as string if sum value is 10 hours and 30minutes it shows as 103000 pls advice

RAW Query is not recommended. It's not safe at all. @dilfdo You can found the answer in Carbon documentation. https://github.com/briannesbitt/Carbon#difference

It is safe as you made it safe, it does not have parameters and if you need them you could use binding.

@dilfdo this should fix your problem: SELECT time_sheet_id, time(sum( TIMEDIFF( shift_end_time, shift_start_time ) )) FROM yourtable

@devtraining : im getting following error

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT time(sum(TIMEDIFF( shift_end_time, shift_start_time ))) from `shifts` whe' at line 1 (SQL: select SELECT time(sum(TIMEDIFF( shift_end_time, shift_start_time ))) from `shifts` where `time_sheet_id` = 29)


                $no_of_hours = DB::Table('shifts')
                    ->where('time_sheet_id','=', $timesheet_id->id)
                     ->selectRaw('SELECT time(sum(TIMEDIFF( shift_end_time, shift_start_time )))')
                    ->get();

@dilfdo just remove 'SELECT' in your query. It should be like this;

$no_of_hours = DB::Table('shifts')
        ->where('time_sheet_id','=', $timesheet_id->id)
        ->selectRaw('time(sum(TIMEDIFF( shift_end_time, shift_start_time ))) as total')
        ->get();

    return $no_of_hours;

@dilfdo you are looking for a query like:

select
time_sheet_id, 
SUM(TIME_TO_SEC(TIMEDIFF(shift_start_time, shift_time_end))) as shift_length
from your_table_name
group by time_sheet_id

This will return the total shift length per shift_id in seconds. Something like:

• 35 - 17805
• 36 - 28904

You can just divide the result by 3600 for hours.

I hope this is what you were looking for.

@spyworld what you wrote is for calculating time diff for each time_sheet_id,

what am trying to do is to sum all these hours, so i get total working hours for example.

would you please help with this ?

Thanks in advance

Do the above then add results.

If you follow the advice above and get the total number of hours for each timesheet, your result set should be a collection which you can then just use the sum() method on to sum the results.

Remember too that if you have multiple users tracking time sheets, you'll need to take that into consideration and include a user_id field in your results.

This is very helpfull but i want to find a difference between date plz tellme about it @sabuncuserhat @dilfdo