按小时获取统计数据，限制为最近6小时

Question

I have a table called uploads(id,file,date) where date is a DATETIME, i want to show a stats board in my website of the latest 6 hours uploads.

Something like:

13:00 : 12,200 Uploads
14:00 : 30,455 Uploads
15:00 : 15,202 Uploads
16:00 : 61,014 Uploads
17:00 : 55,104 Uploads
18:00 : 43,019 Uploads

Thanks

Answer 1

Using:

  SELECT DATE_FORMAT(u.date, '%H:00') AS hr,
         COUNT(*) AS numUploads
    FROM UPLOADS u
   WHERE u.date >= DATE_SUB(NOW(), INTERVAL 6 HOUR)
GROUP BY DATE_FORMAT(u.date, '%H:00')
ORDER BY hr 

...will return:

hr     numUploads
------------------
13:00  12200
14:00  30455
15:00  15202
16:00  61014
17:00  55104
18:00  43019

Caveats

• NOW() includes the time portion when the query was run. Meaning, it could be 31 minutes past the hour, so the values likely will change for the last hour displayed.
• If there aren't any uploads though an entire hour that is in scope of the last six hours, it won't be displayed -- you'll need to LEFT JOIN to a table of derived time values to see a zero count.

PHP

$query = "SELECT DATE_FORMAT(u.date, '%H:00') AS hr,
                 COUNT(*) AS numUploads
            FROM UPLOADS u
           WHERE u.date >= DATE_SUB(NOW(), INTERVAL 6 HOUR)
        GROUP BY DATE_FORMAT(u.date, '%H:00')"

$result = mysql_query($query);

while ($row = mysql_fetch_assoc($result)) {
  echo $row['hr'] .' : '. NUMBER_FORMAT($row['numUploads']) .' Uploads'
}