Show number of visits in the last 24 hours, broken down by the hour...

Question

Hi there,

I'm storing a timestamp in a mysql table every time somebody visits the site for the first time.

I wind up with data that looks like this:

2009-08-02 04:08:27
2009-08-02 04:07:47
2009-08-02 05:58:13
2009-08-02 06:28:23
2009-08-02 06:34:22
2009-08-02 08:23:21
2009-08-02 09:38:56

What Im wanting to do with this data is create a count of visits that fall into each hour. So in the example above I would arrive at the 4th hour having 2 visits, the 5th hour = 1, 6th hour 2, 8th hour 1 etc.

I thought the best way to do this, would be to do a for statement like so:

// a 24 hour loop
for($i = 24; $i > -1; $i--) {

    // the query for each hour
    $sql = 'SELECT * FROM visits WHERE (DATE(added) = DATE_SUB(CURRENT_DATE(), INTERVAL ' . $i . ' HOUR))'

$res = mysql_query($sql);
$count = mysql_num_rows($res);  


    // store the number of rows for this loop in the array
    $visits[] = $count;
}

That seems logical to me... but for some reason... its definitely not working.

How would you do this?

Answer 1

How about:

SELECT hour(added), count(*) 
FROM visits 
WHERE added >= CURRENT_DATE()-1
GROUP BY hour(added)

Hour(time): "Returns the hour for time. The range of the return value is 0 to 23 for time-of-day values",

Answer 2

Hi,

what about doing it in only one query, using a group by clause ?

Something like this would probably do :

select HOUR(added), count(*) as nbr
from visits
where added between '2009-07-14' and '2009-07-15'
group by HOUR(added)
order by HOUR(added)

(of course, you might need to adapt some things, like the dates)

It would make your DB server work more for that query than for the others your proposed... But only one query, and not 24 -- which is a good thing.
On the other side, you'd have less data going from the DB server to the PHP server ; which is great, especially if the table is big !

Then, on the PHP side, you'll have to "reconstruct" the full date, as this query will only get you the hours.