PHP mysql 选择按日期范围分组

Question

我在 MySQL 中有表:

问题:如何按范围内的 1 小时间隔对每一行进行分组？

想要的输出:

range1 = 2014-01-28 00:00:00 to 2014-01-28 01:00:00
range2 = 2014-01-28 01:00:00 to 2014-01-28 02:00:00
range3 = 2014-01-28 02:00:00 to 2014-01-28 03:00:00
...

Array
(
    [range1] => Array
        (
            [id] => 1
            [time] => 2014-01-28 00:00:00
        )

    [range2] => Array
        (
            [id] => 2
            [time] => 2014-01-28 01:05:00
        )

    [range3] => Array
        (
            [id] => 3
            [time] => 2014-01-28 02:00:00
        )

)

我的尝试:

PHP:

function get_data() {
    $start = date("Y-m-d 00:00:00");
    $end = date("Y-m-d H:i:s", strtotime('+1 DAY', strtotime($start)));
    $q = "
        SELECT *
        FROM table
        WHERE (time BETWEEN '" . $start . "' AND '" . $end . "')
    ";
    $select = mysqli_query($this->c, $q);
    while ($row = mysqli_fetch_assoc($select)) {
        $data[] = $row;
    }
    return $data;
}

当前输出:

Array
(
    [0] => Array
        (
            [id] => 1
            [time] => 2014-01-28 00:00:00
        )

    [1] => Array
        (
            [id] => 2
            [time] => 2014-01-28 01:05:00
        )

    [2] => Array
        (
            [id] => 3
            [time] => 2014-01-28 02:00:00
        )

)

Answer 1

SELECT * FROM table WHERE time BETWEEN '$start' AND '$end' GROUP BY DATE(time),DATEPART(hh,time)

此查询将按同一天的小时分组，其中两个值之间的时间