CODEWITHSUNDEEP
laravelphp-carbon
Bruno Albuquerque
Bruno Albuquerque

Reputation: 687

Best way to sum time in Laravel?

I have a table called transactions with two relevant fields to my question, _start_timestamp_ and _end_timestamp_. I need to sum the amount of time passed between all transactions where _end_timestamp_ is not null. So, the result must be something like Total Time of Transactions: 1 hour and 18 minutes

I've tried using Carbon, but I don't know how to sum all the lines of the table using it.

foreach($timestampStarts as $timestampStart){  
    $time = new Carbon($timestampStart->start_timestamp);
    $shift_end_time =new Carbon($timestampStart->end_timestamp);
    dd($time->diffForHumans($shift_end_time));
}

Upvotes: 1

Views: 2841

Answers (2)

Chin Leung
Chin Leung

Reputation: 14941

You can use the MySQL TIMESTAMPDIFF function to calculate the difference:

Transaction::whereNotNull('_end_timestamp_')
    ->sum(DB::raw('TIMESTAMPDIFF(SECOND, _start_timestamp_, _end_timestamp_)'));

This will give you the total in seconds.

Upvotes: 5

Lijesh Shakya
Lijesh Shakya

Reputation: 2540

You need to retrieve the total difference in minutes. Add the total difference and retrieve the diff for humans. You can retrieve like below:

$diffForHumans = null;
$nowDate = Carbon::now();
$diffInMinutes = 0;

foreach($timestampStarts as $timestampStart){  

    if(!empty($timestampStart->end_timestamp)){ // if the end timestamp is not empty

        $time = new Carbon($timestampStart->start_timestamp);
        $shift_end_time =new Carbon($timestampStart->end_timestamp);

        //Adding difference in minutes
        $diffInMinutes+= $shift_end_time->diffInMinutes($time);
    }
}
$totalDiffForHumans = $now->addMinutes($diffInMinutes)->diffForHumans();

Upvotes: 1

Related Questions