phplaravelgroupingphp-carbon

Group laravel collection by 5 minutes


I have Laravel collection consisting data from 11:47 to 12:17:

Illuminate\Support\Collection Object
(
    [items:protected] => Array

        (
            [2020-03-17 11:47:00] => Array
                (
                    [avg] => 4.0666666666667
                )

            [2020-03-17 11:48:00] => Array
                (
                    [avg] => 4.8095238095238
                )

            ...

            [2020-03-17 12:17:00] => Array
                (
                    [avg] => 1
                )

        )
)

I need help to group this collection by 5 minutes starting from first date. If I try to group it this way:

$groupedBy5Minutes = $collection->groupBy(function ($item, $key) {
    return Carbon::parse($key)->timestamp - Carbon::parse($key)->timestamp % (300);
});

I get result grouped by 5 minutes but starting from 11:45 (11:45-12:15) rather than 11:47 (11:47:12:17).


Solution

  • First, I reproduced your collection as follows. The avg is a random number

    $period = Carbon::parse('2020-03-17 11:47:00')->toPeriod('2020-03-17 12:17:00', 1, 'minutes')->toArray();
    /* 
    [
        '2020-03-17 11:47:00',
        '2020-03-17 11:48:00',
        ...,
        '2020-03-17 12:17:00'
    ]
    */
    
    $collection = collect($period)->mapWithKeys(fn($item) =>
        [$item->format('Y-m-d H:i:s') => ['avg' => rand(1,1000)]]
    );
    /*
    Illuminate\Support\Collection {
        all: [    
            '2020-03-17 11:47:00' => ['avg' => 134],
            '2020-03-17 11:48:00' => ['avg' => 545],
            ...,
            '2020-03-17 12:17:00' => ['avg' => 654]
        ]
    }
    */
    

    To get what you want, there's a bit of a trick you can use with Carbon's floor functions:

    $interval = Carbon::parse($collection->keys()->first())
      ->floorMinutes(5)
      ->diff($collection->keys()->first());
    
    $grouped = $collection->groupBy(fn($item, $key) =>
        Carbon::parse($key)
        ->floorMinutes(5)
        ->add($interval)
        ->format('Y-m-d H:i:s')
    );
    // Using classic closures
    $grouped = $collection->groupBy(function ($item, $key) use ($interval) {
        return Carbon::parse($key)
        ->floorMinutes(5)
        ->add($interval)
        ->format('Y-m-d H:i:s');
    });
    /*
    Illuminate\Support\Collection {
        all: [    
            '2020-03-17 11:47:00' => Illuminate\Support\Collection {
                all: [
                    ['avg' => 134],
                    ['avg' => 545],
                    ['avg' => 232],
                    ['avg' => 654],
                    ['avg' => 123]
                ]
            },
            '2020-03-17 11:52:00' => Illuminate\Support\Collection {
                all: [
                    ['avg' => 463],
                    ['avg' => 765],
                    ['avg' => 732],
                    ['avg' => 464],
                    ['avg' => 512]
                ]
            },
            ...,
            '2020-03-17 12:17:00' => Illuminate\Support\Collection {
                all: [
                    ['avg' => 873],
                    ['avg' => 797],
                    ['avg' => 694],
                    ['avg' => 754],
                    ['avg' => 654]
                ]
            }
        ]
    }
    */
    

    Basically we're grouping by an interval of 5 minutes (11:45, 11:50, ..., 12:15) but adding to each key the difference between the first key (11:47) and the floored value to the last 5 minutes (11:45).

    11:45 + (11:47 - 11:45) = 11:47
    11:50 + (11:47 - 11:45) = 11:52
    11:55 + (11:47 - 11:45) = 11:57
    12:00 + (11:47 - 11:45) = 12:02