Laravel Carbon - Calculate time between database entries

Question

I am trying to calculate de difference between two datetimes.

I have two Models, tasks and task_interactions, they are related like this: tasks has many task_interactions, and task_interactions belongs to tasks.

The user can change the status of a task to one of these: "In progress", "Paused" and Finished. The user change the status several times between "In Progress" and "Paused", but once it's finished, the user cannot change the task status anymore.

All interactions are saved in the task_interactions table, with created_by as user_id and created_at as the datetime. the table has these columns: id ; task_id ; status ; comments ; created_by ; created_at.

Here's na exemple for task id = 1:

# id, task_id, status, comments, created_by, created_at
144, 1, In Progress, , 1, 2017-11-14 09:42:20
145, 1, Paused, , 1, 2017-11-14 09:45:53
146, 1, In Progress, , 1, 2017-11-14 09:46:57
147, 1, Paused, , 1, 2017-11-14 09:49:57
148, 1, In Progress, , 1, 2017-11-14 10:00:10
149, 1, Paused, , 1, 2017-11-14 10:06:25
150, 1, In Progress, , 1, 2017-11-14 10:07:26
151, 1, Paused, , 1, 2017-11-14 10:08:11
153, 1, Paused, , 1, 2017-11-14 11:27:04
154, 1, In Progress, , 1, 2017-11-14 11:27:21
155, 1, Paused, , 1, 2017-11-14 11:57:38

The thing is, for me to have the total time spent in one task, i have to determine the time between In Progress and Paused. The time between Paused and In Progress is not relevant.

I am strugling with this. Is there anyone who can help me?

Thanks in advance.

Just one more thing, i am not very experienced, so pardon if my questions sound a bit dummer, I believe that i should do a @foreach loop through all task_interactions where task_id = X, and thats all i know.

As asked, here's the data i am passing for the view:

public function mytasks()
    {
        $users = User::all();
        $mytasks = Task::where('assigned_to','=', Auth::id())->where('status','!=','Finished')->get(); 
        $mytaskscount = Task::where('assigned_to','=', Auth::id())->count();
        $tasksinprogress = Task::where('status','=','In Progress')->where('assigned_to','=', Auth::id())->get();
        $tasksinprogresscount = Task::where('status','=','In Progress')->where('assigned_to','=', Auth::id())->count();
        $taskspaused = Task::where('status','=','Paused')->where('assigned_to','=', Auth::id())->get();
        $taskspausedcount = Task::where('status','=','Paused')->where('assigned_to','=', Auth::id())->count();
        $tasksfinished = Task::where('status','=','Finished')->where('assigned_to','=', Auth::id())->get();
        $tasksfinishedcount = Task::where('status','=','Finished')->where('assigned_to','=', Auth::id())->count();
        $tasktypes = Tasktype::all();
        $chapters = Chapter::all();

        return view('pages.tasks_my', compact('mytasks','tasktypes','chapters','tasksinprogress','taskspaused','tasksfinished','mytaskscount','tasksinprogresscount','taskspausedcount','tasksfinishedcount'));
    }

And one more thing, what i know so far

@foreach ($tasks as $task)
    @foreach ($task->interactions as $interactions)
{{ In here, every time my loop returns a status = "In progress" i should mark that time, and if that the next loop returns a "Paused", i should count the difference between these two. And sum all other equal cases in the loop. }}
    @endforeach
@endforeach

I realy have no idea on how to do that

Answer 1

I would recommend David's answer. If you are not comfortable doing it using MySQL. You may have to do it like this:

Calculate difference between first time a task was started & when it was paused:

$start = Carbon::parse($startTask->created_at);
$pause = Carbon::parse($endTask->created_at);

$diffInSeconds = $pause->diffInSeconds($start)

This should give you the total difference in seconds as to when a tasks was started & then paused. Now you have to repeat this approach for when again a task was started & then paused.

Answer 2

This is strict Mysql, but should do the trick. You can call this in laravel directly using raw sql instruction, or you should probably rework this into Laravel code.

select SEC_TO_TIME(
    sum( 
        timediff(
            if(task_end.created_at is null, 
               NOW(), 
               task_end.created_at), 
            task_start.created_at)))
from task_interactions task_start
left join task_interactions task_end
    ON task_end.task_id = task_start.task_id
    AND task_end.status IN ('Pause', 'Finished')
    AND task_end.created_at = (
        SELECT min(created_at)
        FROM task_interactions
        WHERE status = task_end.status
        AND created_at >= task_start.created_at
    )
WHERE task_start.task_id = 1
AND task_start.status = 'In Progress'