如何在php / mysql中对时间间隔进行分组并根据这些时间组获取统计信息？

I am new to php and I have a problem with time/date manipulation.

我是php新手，我的时间/日期操作有问题。

I need to make statistics about daily/monthly/yearly visits in some store. There is a mysql database with table "statistics" and fields: "statistic_id" ( integer, primary key ) , "visitors" ( integer ), and "dateAndTime" ( timestamp ). I have a form where user enter number of visitors that came to the store, and that number is inserted in database along with the time and date using now() function.

我需要对某些商店的每日/每月/每年访问进行统计。有一个带有“统计”表和字段的mysql数据库：“statistic_id”（整数，主键），“visitor”（整数）和“dateAndTime”（时间戳）。我有一个表单，用户输入来到商店的访客数量，并使用now（）函数将该数字与时间和日期一起插入数据库。

So basically my database looks like :

基本上我的数据库看起来像：

statistic_id , visitors , timeAndDate <br />
1............, 3........., 2012-09-29 14:45:02 <br />
2............, 5........., 2012-09-29 14:46:31 <br />
3............, 2........., 2012-09-29 18:48:11 ...etc.

What I need to do is sum and display all visitors that came in specific time interval. 09h-12h ; 12h-15h ; 15h-18h ; 18h-21h . So I need to display in table all these intervals and number of visitors for each of them. The problem is I don't know how to extract these intervals and then get sum of visitors for them. I was trying everything I know and I could find, but without success. Also I will have to get monthly and yearly statistics, so how can I get all 12 months from this timeAndDate column and then sum all visitors for each month ?

我需要做的是总结并显示特定时间间隔内的所有访问者。 09h-12h; 12h-15h; 15h-18h; 18h-21h。因此，我需要在表格中显示所有这些间隔和每个访问者的数量。问题是我不知道如何提取这些间隔，然后获得他们的访客总和。我正在尝试我所知道的一切，但我找不到，但没有成功。此外，我还需要获得月度和年度统计数据，那么如何从这个时间和日期列中获取所有12个月，然后将每个月的所有访问者相加？

Do anyone have an idea how to do this, and is willing to explain me in details please ? Thanks

有谁知道如何做到这一点，并愿意详细解释我吗？谢谢

3 个解决方案

#1

To get visitors between 09:00 and 12:00

在09:00至12:00之间吸引访客

SELECT
    SUM(`visitors`)

FROM
    `my_table`

WHERE
    HOUR(`timeAndDate`) BETWEEN 9 AND 12

To get visitors by month

按月获得访客

SELECT
    MONTH(`timeAndDate`),
    SUM(`visitors`)

FROM
    `my_table`

GROUP BY
    MONTH(`timeAndDate`)

To get visitors by year

每年吸引游客

SELECT
    YEAR(`timeAndDate`),
    SUM(`visitors`)

FROM
    `my_table`

GROUP BY
    YEAR(`timeAndDate`)

Simple PHP example to output the months in a table

简单的PHP示例，用于输出表中的月份

<?php

    // Connect to database
    $db = mysqli_connect('localhost', 'root', 'root', 'test') or die('Could not connect to database');

    // Prepare sql question
    $sql = "SELECT
                MONTHNAME(`timeAndDate`) AS `month`,
                SUM(`visitors`) AS `visitors`

            FROM
                `test`

            GROUP BY
                MONTH(`timeAndDate`)";

    // Query the database
    $result = mysqli_query($db, $sql);

    // Begin table
    print '<table><thead><tr><th>Month</th><th>Visitors</th></tr></thead><tbody>';

    // Loop result
    while($row = mysqli_fetch_assoc($result)) {
        print "<tr><td>{$row['month']}</td><td>{$row['visitors']}</td></tr>";
    }

    // End table
    print '</tbody></table>';

?>

#2

WHERE DATE_FORMAT(timeAndDate,'%k') BETWEEN 9 AND 12

在9和12之间的DATE_FORMAT（timeAndDate，'％k'）

DATE_FORMAT : http://www.w3schools.com/sql/func_date_format.asp

DATE_FORMAT：http：//www.w3schools.com/sql/func_date_format.asp

#3

SELECT DATE(timeAndDate), SUM(visitors) FROM Table
WHERE 
DATE_FORMAT(timeAndDate,'%k') BETWEEN 9 AND 12
AND YEAR(timeAndDate) = 2012
GROUP BY MONTH(timeAndDate)

#1