CODEWITHSUNDEEP
phpsqllaraveldatetimeeloquent
MuZak
MuZak

Reputation: 301

How to use aggregate functions on the time of a datetime column with Eloquent?

I have the following SQL table :

|---------------------------------------------|
| LESSONS                                     |
|-----------------------|---------------------|
| BEGIN_DATE (DateTime) | END_DATE (DateTime) |
|-----------------------|---------------------|
|  2019-04-22 10:00:00  | 2019-04-22 12:00:00 |
|  2019-04-23 09:00:00  | 2019-04-23 10:00:00 |

Using Laravel Eloquent, I want to get the minimum hour of all lessons for the BEGIN_DATE column, and the maximum hour from the END_DATE column. In this case, I would like to have 09:00:00 for the minimal hour, and 12:00:00 for the maximal hour.

I first tried Lesson::min('begin_date') and Lesson::max('end_date') but it obviously returned the minimal and maximal DateTime, not Time.

I also tried

Lesson::select('select cast(begin_datetime as time)')
    ->where('season_id', $this->id)->min(time);

but I get an SQL error:

'COLUMN NOT FOUND` for the time alias.

Also, I would prefer a solution only with Eloquent, using no SQL.

How can I do this?

Upvotes: 1

Views: 169

Answers (1)

Kenny Horna
Kenny Horna

Reputation: 14241

You could do this:

$data = Lesson::selectRaw(
            'min(TIME(begin_date)) as min_time, max(TIME(end_date)) as max_time'
        )
        ->first()
        ->toArray();

This should return something like this:

dd($data);

=> [
     "min_time" => "09:00:00",
     "max_time" => "12:00:00",
   ]

I'd also like to see an Eloquent approach on this (tried but failed lol). I Hope this helps.

Upvotes: 1

Related Questions