How to get duration between mysql records created_at timestamp using laravel 8

I would like to find out how long an event lasted without having to add a finish time to that event.

To demonstrate I have created a simple exercise:

events table:

Schema::create('events', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->timestamps();
});

INSERT INTO `events` (`id`, `name`, `created_at`, `updated_at`) VALUES
(1, 'Event 1', '2021-08-19 10:41:16', '2021-08-19 10:41:16'),
(2, 'Event 2', '2021-08-19 11:11:25', '2021-08-19 11:11:25'),
(3, 'Event 3', '2021-08-19 11:25:21', '2021-08-19 11:25:21'),
(4, 'Event 4', '2021-08-19 11:35:28', '2021-08-19 11:35:28'),
(5, 'Event 5', '2021-08-19 11:45:38', '2021-08-19 11:45:38');

I am currently pulling the data out of the database with:

public function index()
{   
    return Event::get();
}

which returns the following:

{
    id: 1,
    name: "Event 1",
    created_at: "2021-08-19T10:41:16.000000Z",
    updated_at: "2021-08-19T10:41:16.000000Z"
},
{
    id: 2,
    name: "Event 2",
    created_at: "2021-08-19T11:11:25.000000Z",
    updated_at: "2021-08-19T11:11:25.000000Z"
},
{
    id: 3,
    name: "Event 3",
    created_at: "2021-08-19T11:25:21.000000Z",
    updated_at: "2021-08-19T11:25:21.000000Z"
},
{
    id: 4,
    name: "Event 4",
    created_at: "2021-08-19T11:35:28.000000Z",
    updated_at: "2021-08-19T11:35:28.000000Z"
},
{
    id: 5,
    name: "Event 5",
    created_at: "2021-08-19T11:45:38.000000Z",
    updated_at: "2021-08-19T11:45:38.000000Z"
}

I would like to take the start time for the next event and use it as the finish time to calculate a duration, if there is no next event then it is assumed that event is still running so the duration should be calculated using the current time.

So say I ran the query at 13:00 I would expect the following results (duration is calculated in seconds):

[
    {
    id: 1,
    name: "Event 1",
    created_at: "2021-08-19T10:41:16.000000Z",
    updated_at: "2021-08-19T10:41:16.000000Z",
    duration:   "1809"
    },
    {
    id: 2,
    name: "Event 2",
    created_at: "2021-08-19T11:11:25.000000Z",
    updated_at: "2021-08-19T11:11:25.000000Z",
    duration:   "836"
    },
    {
    id: 3,
    name: "Event 3",
    created_at: "2021-08-19T11:25:21.000000Z",
    updated_at: "2021-08-19T11:25:21.000000Z",
    duration:   "607"
    },
    {
    id: 4,
    name: "Event 4",
    created_at: "2021-08-19T11:35:28.000000Z",
    updated_at: "2021-08-19T11:35:28.000000Z",
    duration:   "610"
    },
    {
    id: 5,
    name: "Event 5",
    created_at: "2021-08-19T11:45:38.000000Z",
    updated_at: "2021-08-19T11:45:38.000000Z",
    duration:   "4462"
    }
]

Any advice on how to achieve this? can it be achieved with eloquent or would it be better to utilise something like collections and calculate the durations after the data is pulled from the DB?

Answer

You can use Collection::map() in combination with Carbon::diffInMinutes() (or however you want to diff):

public function index()
{   
    return Event::get()->map(function($event, $key) use($events) {
        $event->duration = 0;
        if ($key < $events->count() - 1) {
            $event->duration = $events[$key + 1]->created_at->diffInMinutes($event->created_at);
        }
        return $event;
    }));
}

---

If you don’t like to do this in your controller, you can do it directly in the collection instead:

1. Create a new collection via php artisan make:collection \App\Collections\EventCollection
2. Do the following:

<?php

namespace AppCollections;

use DatePeriod;
use IlluminateDatabaseEloquentCollection;
 
class EventCollection extends Collection
{
    public function withDuration()
    {
        return $this->map(function($event, $key) {
            $event->duration = 0;
            if ($key < $this->count() - 1) {
                $event->duration = $this[$key + 1]->created_at->diffInMinutes($event->created_at);
            }
            return $event;
        });
    }
}

3. Inside your Event model add this:

public function newCollection(array $models = [])
{   
    return new EventCollection($models);
}

4. Use it like this:

public function index()
{   
    return Event::get()->withDuration();
}