List hits per hour from a MySQL table

Question

I am trying to work out the hits per hour from a database. Data basically is stored as follows :

Table Name: Visitors
============================
VisitorIP          TIMESTAMP
----------------------------
15.215.65.65       123456789

I want to display total hits per hour (within the last 6 hours ) including the hours that has no hits. Example of the output:

// Assuming now : 21:00
21:00 - 0 hits
20:00 - 1 hits
19:00 - 4 hits
18:00 - 0 hits
17:00 - 2 hits
16:00 - 3 hits

i would love to get the data as array, Please note that the stored date is in UNIX time stamp format. and there may be some hours without any hits!

Thanks

Answer 1

You can use simple keyword BETWEEN for it. You can describe datetime is between values you want to select (range 3600 seconds).

SELECT * FROM VISITORS WHERE TIMESTAMP BETWEEN UNIX_TIMESTAMP()-3600*1 AND UNIX_TIMESTAMP()-3600*0;
SELECT * FROM VISITORS WHERE TIMESTAMP BETWEEN UNIX_TIMESTAMP()-3600*2 AND UNIX_TIMESTAMP()-3600*1;
SELECT * FROM VISITORS WHERE TIMESTAMP BETWEEN UNIX_TIMESTAMP()-3600*3 AND UNIX_TIMESTAMP()-3600*2;
SELECT * FROM VISITORS WHERE TIMESTAMP BETWEEN UNIX_TIMESTAMP()-3600*4 AND UNIX_TIMESTAMP()-3600*3;
SELECT * FROM VISITORS WHERE TIMESTAMP BETWEEN UNIX_TIMESTAMP()-3600*5 AND UNIX_TIMESTAMP()-3600*4;
SELECT * FROM VISITORS WHERE TIMESTAMP BETWEEN UNIX_TIMESTAMP()-3600*6 AND UNIX_TIMESTAMP()-3600*5;

Answer 2

SELECT MONTH(timestamp),DAY(timestamp), HOUR(timestamp),count(*) 
 from visitors
  group by MONTH(timestamp), DAY(timestamp), HOUR(timestamp)

In php you would need to account for any holes in the data from missing hours.

Answer 3

just simplified above answer,

for($i=1;$i<=6;$i++){
  $s -= $i;    
  $query[$i] = "SELECT * FROM VISITORS WHERE TIMESTAMP BETWEEN UNIX_TIMESTAMP()-3600*$i AND UNIX_TIMESTAMP()-3600*$s";
}

$i = 1;
foreach($query as $value){
  $result[$i] = mysql_fetch_row($query[$i]);
  $i++;
}

just my simple idea. feel free to use.