How to group records by time range?

Question

I have a table in Mysql, which stores visits on my site, having as fields: id | ip | date, being the date of the kind DATETIME.

What I need is to separate these visits by peak hours.

Example:

Das 10:00 às 12:00 | 500 visitas
Das 14:00 às 16:00 | 800 visitas

What I have today is a list of times and next to the number of visits, regardless of the amount.

Das 06:00 às 08:00 | 220
Das 08:00 às 10:00 | 410
Das 10:00 às 12:00 | 105

I need to sort by the time you’ve had more visitors, how to do this?

What I got now is this:

<?php 
$sel_visitas = mysql_query("SELECT * FROM acessos_site"); 
if(mysql_num_rows($sel_visitas) >= 1){ 
    $sel_00_06 = mysql_query("SELECT * FROM acessos_site WHERE TIME(data) BETWEEN '00:00:00' AND '06:00:00'");
    $visitas_00_06 = mysql_num_rows($sel_00_06); 
?> 
<table border="0" style="width:940px;"> 
    <tr class="tit"> 
        <td>Horário</td> 
        <td>Visitas</td> 
    <tr> 
    <tr> 
        <td>Das 00:00 às 06:00</td> 
        <td><?php echo $visitas_00_06;?></td> 
    </tr>
</table>

Answer 1

You can use Mysql functions to handle(CASE), group(GROUP_BY) and sort the results(ORDER BY):

SELECT tempo,count(*) as quantidade
FROM (
  SELECT 
  (CASE
  WHEN DATE_FORMAT(data, '%H:%i:%s') > '07:00' 
  && DATE_FORMAT(data, '%H:%i:%s') < '09:00' THEN '07h às 09h'
  WHEN DATE_FORMAT(data, '%H:%i:%s') > '12:00' 
  && DATE_FORMAT(data, '%H:%i:%s') < '14:00' THEN '12h às 14h'
  WHEN DATE_FORMAT(data, '%H:%i:%s') > '19:00' 
  && DATE_FORMAT(data, '%H:%i:%s') < '21:00' THEN '19h às 21h'
  WHEN DATE_FORMAT(data, '%H:%i:%s') > '21:00' 
  && DATE_FORMAT(data, '%H:%i:%s') < '23:00' THEN '21h às 23h'
   ELSE 0 END)
  AS tempo
  FROM base
) as acessos    
GROUP BY tempo
ORDER BY quantidade DESC

Sqlfiddle

I also used the DATE_FORMAT to return only the time. And in relation to PHP the extension mysql was discontinued, use the mysqli.

Answer 2

If you want to find out the peak time according to all your site’s visit history:

SELECT
    HOUR(data) AS hora,
    COUNT(*) AS acessos
FROM acessos_site
GROUP BY hora
ORDER BY acessos DESC;

If you want to find out the peak time of each day:

SELECT
    YEAR(data) AS ano,
    MONTH(data) AS mes,
    DAYOFMONTH(data) AS dia,
    count(*) AS acessos
FROM acessos_site
GROUP BY ano, mes, dia
ORDER BY acessos DESC;

Answer 3

Come on I believe this will solve for you:

SELECT * FROM visitas WHERE HOUR(data) >= 10 and HOUR(data) <= 12;
SELECT COUNT(*) as nVisitas FROM visitas WHERE HOUR(data) >= 10 and HOUR(data) <= 12;

From a tested, in case it doesn’t solve your case post here again I try to find another solution....