How to group records by time zone?

Question

How to group records by time zone?

Navigation

#1 by (5 votes)
#2 by (2 votes)
#3 by (0 votes)

7

I have a table in MySQL, which stores the visits on my site, taking as fields: id | ip | date , with date of type DATETIME .

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

Example:

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

What I have today is the list of times and the number of visits regardless of quantity.

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 I had more visits, how to do this?

What I have 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>

php mysql

asked by anonymous 16.03.2014 / 22:29

3 answers

How to download an image from the internet with C #? Make two different classes communicate

score 5 · Answer 1

You can use functions of MySQL itself to treat ( CASE ), group ( GROUP_BY ) and sort 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 use DATE_FORMAT to return only the time. And regarding PHP the mysql extension has been discontinued, use mysqli .

score 2 · Answer 2

If you want to discover the peak times according to your site's entire 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 for 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;

score 0 · 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;

Of a test, if this does not solve your case re-post here I try to find another solution ....