List of hits per hour from MySQL table

Question

I am trying to parse hits per hour from a database. Data is mainly stored as follows:

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

I want to show the total number of views per hour (within the last 6 hours), including hours in which there are no hits. Output Example:

// 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 like to receive the data as an array. Note that the saved date is in UNIX timestamp format. and maybe a few hours without any hits!

thank

Answer 1

You can use the simple BETWEEN keyword for this. You can describe the date and time between the 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)

php .

Answer 3

,

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.