Agrupar la consulta de mysql por intervalos de 15 minutos

Solución:

SELECT   FLOOR(UNIX_TIMESTAMP(timestamp)/(15 * 60)) AS timekey
FROM     table
GROUP BY timekey;

Pruebe esto, agrupación de registros de intervalo de 15 minutos, puede cambiar 15 * 60 al intervalo en segundos que necesita

SELECT sec_to_time(time_to_sec(datefield)- time_to_sec(datefield)%(15*60)) as intervals from tablename
group by intervals

Adaptación del enfoque 1) a continuación:

select Round(date_format(date, "%i") / (15*60)) AS interval  
from table
group by interval 

Adaptación del enfoque 3) a continuación:

SELECT Round(Convert(substring(date_column, 14, 2), UNSIGNED) / (15*60)) AS interval /* e.g. 2009-01-04 12:20:00 */
FROM table 
GROUP BY interval;

Algunos enfoques que he encontrado aquí:

1)

select date_format(date, "%W") AS `Day of the week`, sum(cost)
from daily_cost
group by `Day of the week` 
order by date_format(date, "%w")

2)

select count(*) as 'count', 
  date_format(min(added_on), '%Y-%M-%d') as 'week commencing',
  date_format(added_on, '%Y%u') as 'week' 
from system 
where added_on >= '2007-05-16' 
group by week 
order by 3 desc;

3)

SELECT substring(postdate, 1,10) AS dd, COUNT(id) FROM MyTable GROUP BY dd;

(También aquí: http://www.bradino.com/mysql/dayparting-on-datetime-field-using-substring/)

EDITAR: Todas las soluciones funcionarán mal en una mesa con una gran cantidad de registros.

---

---

Utiliza Nuestro Buscador