如何从毫秒列中按月将MYSQL db的结果分组？

Question

I'm trying to get total number of item based on month from MSQL database using SLIM framework, When i display data i get result but separated even if there are in the same month. My code are,

    $app->get('/view/test', function ($request, $response, array $args) {
      try {
    $con = $this->db;
    date_default_timezone_set('UTC+3h');
    $sql = "SELECT start_time,COUNT(*) as tt FROM parking_sessions GROUP 
     BY FROM_UNIXTIME(start_time/1000)";
    $result = null;
    $results = null;
    foreach ($con->query($sql) as $row) {

        $result['muda'] = date('M',($row['start_time']/1000));
        $result['total'] = $row['tt']; 
        $results[] =$result; 
        $total = count($results);
    }
    if ($result) {
        return $response->withJson(array('status' => 'true', 'date' => 
        $results, 'jumla' => $total), 200);
    } else {
        return $response->withJson(array('status' => 'Owner Not Found'), 
         422);
    }

} catch (\Exception $ex) {
    return $response->withJson(array('error' => $ex->getMessage()), 422);
}

});

And it give me the following result

      "date": [
    {
        "month": "Feb",
        "total": "1"
    },
    {
        "month": "Mar",
        "total": "1"
    },
    {
        "month": "Apr",
        "total": "1"
    },
    {
        "month": "Apr",
        "total": "1"
    }
],

And all result needed is like below

    "date": [
    {
        "month": "Feb",
        "total": "1"
    },
    {
        "month": "Mar",
        "total": "1"
    },
    {
        "month": "Apr",
        "total": "2"
    }
],

And my DB look like

    id slot_id    customer_vehicle_id    start_time        end_time             check_out_time      status


    1    1              2                1553249524000     1554346841515            1553111000          1

    2    6              4                1554351619803     1553253124000            1553214094          0

    3    6              4                1555224715000     1553253124000            1553214094          0

    4    7              3                5086800000        1553253124000            1553214094          0

Answer 1

You cannot group by queries with unixtime as your getting every different second and minute in unix time. what you need is to convert it to date without time. SELECT start_time,COUNT(*) as tt FROM parking_sessions GROUP BY DATE (FROM_UNIXTIME(start_time/1000)) note: date will truncate minutes and you'll left with date only