How to Group result from MSQL db by month from a milliseconds column?

26
April 15, 2019, at 12:30 PM

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

READ ALSO
i am getting error while using NEW.id in database triggers

i am getting error while using NEW.id in database triggers

i am trying to create a trigger for inserting dataand I want to get the current id for that author or user who is inserting or change in table

28
Creating Unique Index And Insert Data

Creating Unique Index And Insert Data

I am making different question regarding on my problemThey suggest me on this thread Best way to avoid duplicate entry into mysql database Thank you for pointing out this thread on me

22
How can I set up password-less in ssh2_connect login?

How can I set up password-less in ssh2_connect login?

I have gone through the post where I can do ssh without giving password [https://askubuntucom/a/296574/905016] But I want to login through ssh2_connect in php, where I need to give password detail

44
search nested array of objects and return full parents as results | javascript

search nested array of objects and return full parents as results | javascript

Hello I need a bit of help finishing up my functionI've successfully written a recursive function to loop through a nested object and find the results

34