CODEWITHSUNDEEP
phpmysql
Darren
Darren

Reputation: 13128

Splitting data based on time(hour) efficiently

What I'm trying to do is essentially group amounts of "votes" in relation to the hours of the day for the current day.

Say I have data like this:

$array = array(
    array('vote' => 'up', 'timestamp' => '1460514600'),    //10.30am
    array('vote' => 'up', 'timestamp' => '1460514600'),    //10.30am
    array('vote' => 'down', 'timestamp' => '1460514600'),  //10.30am
    array('vote' => 'up', 'timestamp' => '1460514600'),    //10.30am
    array('vote' => 'down', 'timestamp' => '1460514600'),  //10.30am
    array('vote' => 'up', 'timestamp' => '1460529000'),    //2.30pm
    array('vote' => 'up', 'timestamp' => '1460529000'),    //2.30pm
    array('vote' => 'down', 'timestamp' => '1460529000'),  //2.30pm
);

I currently have the following to section it into 24-hour based hours:

$new = array();
foreach($array as $element){
    $hour = date('H', $element['timestamp']);
    if(!isset($new[$hour])){
        $new[$hour] = array(
            'up' => 0,
            'down' => 0,
        );
    }
    // check & add
    switch($element['vote']){
        case "up":
            $new[$hour]['up']++;
            break;
        case "down":
            $new[$hour]['down']++;
            break;
    }

}

Which returns the data as desired:

Array
(
    [10] => Array
        (
            [up] => 3
            [down] => 2
        )

    [14] => Array
        (
            [up] => 2
            [down] => 1
        )

)

Is there a more efficient way of doing this sectioning? Even if it's possible to do it directly from Mysql?

Upvotes: 1

Views: 75

Answers (1)

Kevin
Kevin

Reputation: 41885

No sql guru here, but the closest that I can come up is to just use a simple SUM and CASE to count up all the vote ups and downs with a GROUP BY on the formatted hour (if it is indeed saved as a unix timestamp). 

SELECT 
    DATE_FORMAT(FROM_UNIXTIME(`timestamp`), '%H')
    AS group_hour,
    SUM(CASE WHEN vote = 'up' THEN 1 ELSE 0 END) AS total_up,
    SUM(CASE WHEN vote = 'down' THEN 1 ELSE 0 END) AS total_down
FROM table_name

GROUP BY `group_hour`

Somewhat of a demo

Upvotes: 2

Related Questions