PHP多维数组按15分钟间隔排序

Question

I am trying to sort a Multidimentional array into 15 minute increments with the following data, this is data from a Call Center Database for phone records

CallID      CallDateTime                    IsLost  IsAnswered
82650       May 10 2016 08:55:01:000AM      0       1
82666       May 10 2016 09:03:13:000AM      0       1
82677       May 10 2016 09:07:45:000AM      0       1
82688       May 10 2016 09:12:19:000AM      0       1
82689       May 10 2016 09:14:42:000AM      0       1
82702       May 10 2016 09:22:08:000AM      0       1
82708       May 10 2016 09:26:26:000AM      0       1
82737       May 10 2016 09:39:42:000AM      0       1
82739       May 10 2016 09:43:17:000AM      0       1
82748       May 10 2016 09:45:22:000AM      0       1
82786       May 10 2016 09:59:26:000AM      0       1
82789       May 10 2016 10:00:21:000AM      0       1
82820       May 10 2016 10:26:52:000AM      0       1
82842       May 10 2016 10:37:14:000AM      0       1
82846       May 10 2016 10:39:04:000AM      0       1
82859       May 10 2016 10:48:30:000AM      0       1
82865       May 10 2016 10:50:18:000AM      0       1
82880       May 10 2016 10:56:47:000AM      0       1
82911       May 10 2016 11:07:30:000AM      0       0
82925       May 10 2016 11:15:34:000AM      0       1
82926       May 10 2016 11:16:08:000AM      0       1
82927       May 10 2016 11:16:17:000AM      0       1
82943       May 10 2016 11:23:16:000AM      0       1
82944       May 10 2016 11:25:54:000AM      0       1
82947       May 10 2016 11:26:48:000AM      0       1
82966       May 10 2016 11:33:27:000AM      0       1
83048       May 10 2016 12:14:39:000PM      0       1

That I get from an MSSQL Server Database, placing data in via the following PHP Code

 $sql = "SELECT CallID, CallDateTime, IsLost, IsAnswered FROM vwCustomReportingCallsMain WHERE DDI = '2399' AND datediff(day, CallDateTime, '2016-05-10') = 0 ORDER BY CallDateTime ASC";
foreach ($dbh->query($sql) as $row)
    {
        if($row['CallID'] != $last) {
            $callDataArray[] = array("CallID" => $row['CallID'], "CallDateTime" => $row['CallDateTime'], "IsLost" => $row['IsLost'], "IsAnswered" => $row['IsAnswered']);
        }
        $last = $row['CallID'];
    }

The desired output would be like the following format somehow (data not correct)

Date/Time           IsAnswered IsLost
May 10 2016 08:30   7          0
May 10 2016 09:00   8          0
May 10 2016 09:30   14         0
May 10 2016 10:00   11         0
May 10 2016 10:30   11         0
May 10 2016 11:00   13         0
May 10 2016 11:30   12         0
May 10 2016 12:00   11         0
May 10 2016 12:30   8          0
May 10 2016 13:00   20         0
May 10 2016 13:30   9          0
May 10 2016 14:00   10         0
May 10 2016 14:30   12         0
May 10 2016 15:00   8          0
May 10 2016 15:30   14         0
May 10 2016 16:00   12         0
May 10 2016 16:30   11         0

Any ideas on how to in PHP or even MSSQL PDO ? Any help would be awesome - Cheers

Answer 1

You can use the following code. It may not be perfect but it will give you an idea on how to proceed further.

function roundToNearest30($timestring) {
    $minutes = date('i', $timestring);
    return sprintf("%02d", $minutes - ($minutes % 30));
}

$totalArray = array();
$lastTime = NULL;
foreach($callDataArray as $callData){
    $timeString = strtotime($callData['CallDateTime']);
    $currentHour = date('H', $timeString);
    $nearest30Minutes = roundToNearest30($timeString);
    $nearestTime = "{$currentHour}:{$nearest30Minutes}";

    //create array key based on nearest 30 minutes
    if (!isset($totalArray[$nearestTime])){
        //add the array element as it does not exist
        $totalArray[$nearestTime] = array("TotalLost" => $callData['IsLost'], "TotalAnswered" => $callData['IsAnswered']);
    }else{
        //update they array element with previous data
        $totalArray[$nearestTime]["TotalLost"] += $callData['IsLost'];
        $totalArray[$nearestTime]["TotalAnswered"] += $callData['IsAnswered'];
    }
}

foreach($totalArray as $nearest30 => $data){
    echo "{$nearest30}, Total Lost: {$data['TotalLost']}, Total Answered: {$data['TotalAnswered']}";
    echo "<br>";
}

---

Output:
08:30, Total Lost: 0, Total Answered: 1
09:00, Total Lost: 0, Total Answered: 6
09:30, Total Lost: 0, Total Answered: 4
10:00, Total Lost: 0, Total Answered: 2
10:30, Total Lost: 0, Total Answered: 5
11:00, Total Lost: 0, Total Answered: 6
11:30, Total Lost: 0, Total Answered: 1
12:00, Total Lost: 0, Total Answered: 1