Calculate time difference in Eloquent Query

I have a Collection with the following structure;

Collection {#6467 ▼
  #items: array:7 [▼
    0 => Appointment {#6459 ▼
    ...
      #attributes: array:9 [▼
        "id" => 1092
        "company_id" => 2
        "employee_id" => 5
        "client_id" => 31
        "notes" => "Fugiat reprehenderit cumque rerum error quia aliquam ut autem dolor doloremque adipisci est fugiat quia dolorum ut voluptates itaque qui impedit enim nobis."
        "startDateTime" => "2019-11-18 11:00:00"
        "endDateTime" => "2019-11-18 12:00:00"
        "created_at" => "2019-11-15 18:55:55"
        "updated_at" => "2019-11-15 18:55:55"
      ]
      #original: array:9 []
      ....
     
    1 => All other appointments...

In frontend I need the duration of the appointment. At the moment I am making a foreach loop gping through each appointment, calculate the duration like this:

foreach( $appointments as $appointment)
    $duration = $appointment->endDateTime - $appointment->startDateTime
    //Add to the appointment
    $appointment->duration = $duration
}

It works but since I have potentially hundreds of Appointments (10 per day per each employee per each company) this process slows down the app.

So I wonder if I could calculate the duration (endDateTime - startDateTime) in the query and add it to the collection. So I did this:

$newDay = Appointment::>whereDate('startDateTime', 'like', '%' . $datum . '%')
  ->orderBy('startDateTime')
  ->select(
     'appointments.*',
     'duration',
          function ($q) {
               $q->raw('appointment->endDateTime' - 'appointment->startDateTime');
          }
     )
  ->get();

But I do get the following error:

stripos() expects parameter 1 to be string, object given

What am I doing wrong?

tykus

6 years ago

Level 104

You need to use a SQL function to calculate difference in datetime columns, not PHP object syntax:

// ...
 ->selectRaw('appointments.*, TIMESTAMPDIFF(minute, startDateTime, endDateTime) as duration')

I have specified minute above, so you will get a number of full minutes that have/will pass between the two datetimes. This function will not return fractions of hours, so you will get apparently spurious results if you were to choose hour as the unit.

I would consider adding this as a global scope on the Appointment model so that you will always have the duration - it is likely something you will very often need, and the cost in terms of the query os negligible.

1 like

RafaelMunoznl

6 years ago

Level 8

@tykus Thanks a lot! It wors perfect!

What do you mean with "adding it as Global Scope"?

tykus

6 years ago

Best Answer

Level 104

https://laravel.com/docs/6.x/eloquent#global-scopes

A global scope is defined on the model, in the boot method, and is applied for every SELECT query. In your case, it might look like:

// Appointment.php
public static function boot()
{
    parent::boot();
    
    static::addGlobalScope('duration', function (Builder $builder) {
            if (is_null($builder->getQuery()->columns)) {
            $builder->select($builder->getQuery()->from.'.*');
            }

        $builder->addSelect(
            \DB::raw('TIMESTAMPDIFF(minute, startDateTime, endDateTime) as duration')
        );
    });
}

I have modified the code from previously so that we do not force appointments.* as the selected columns; instead we check if there are no selected columns on the query, and in that case adds appointments.*. In either case, we addSelect with the duration calculation.

1 like

RafaelMunoznl

6 years ago

Level 8

@tykus Thanks a lot!!!

Please or to participate in this conversation.