Select averaged in minutes - Mysql

Question

Below is a query returned in a table of my database (Mysql):

What interests me are the Timestring, Varvalue and id columns. Every 5 seconds on average an Insert is made on my system. I have 1 month Datetime values.

---

Doubt:

How do I set up appointments that call me at 1 minute, 15 minutes, 30 minutes, 1 hour, 1 day, the Varvalue average? You can consult separately, where the user will select the range. That is, if it selects intervals every 1 hour, it should be as follows:

TimeString | VarValue

2017-10-06 23:50:50 | 30,55

2017-10-06 00:50:55 | 31,55

2017-10-06 01:50:55 | 41,55 ...

---

I tried it this way:

SELECT 
MIN(`i_l1`.`TimeString`) as TimeString, 
AVG(`i_l1`.`VarValue`) as VarValue01

FROM `i_l1`

GROUP BY HOUR( `i_l1`.`TimeString` )

Return of consultation:

The problem is that this query is not generating every day, because I have data of the day 06-10 until 16-10, and this disorganized the order of the column Timestring.

Commentary

In SQL Server I had done as follows, but with another table format:

SELECT TOP 24 
CAST(CAST(CAST(CAST(table_c1_fatura.E3TimeStamp as float)*24 as bigint) as float)/24 as datetime) as hora,
CAST(CAST(CAST(CAST(table_c1_fatura.E3TimeStamp as float)*24 as bigint) as float)/24 as datetime)+0.04167 as hora_final,
AVG(table_c1_fatura.[PIT970]) AS campo1,
AVG(table_c1_fatura.[FIT964]) AS campo2,
MIN(table_c1_fatura.[TOTALIZADO_FIT964]) as campo3
FROM table_c1_fatura 
WHERE (E3TimeStamp  >= #<%DataInicial%># AND E3TimeStamp <= #<%DataFinal%># AND table_c1_fatura.[TOTALIZADO_FIT964] <> 0 )
GROUP BY CAST(CAST(CAST(CAST(E3TimeStamp as float)*24 as bigint) as float)/24 as datetime)
ORDER BY Hora ASC

Could be something similar to the above example.

Answer 1

I managed to solve using GROUP BY, follows (opcaoDesejava):

Intervalos por minuto = ... GROUP BY DATE_FORMAT(`i_l1`.`TimeString`, '%Y%m%d%H%i')
Intervalos por hora = ... GROUP BY DATE_FORMAT(`i_l1`.`TimeString`, '%Y%m%d%H')
Intervalos por dia = ... GROUP BY DATE_FORMAT(`i_l1`.`TimeString`, '%Y%m%d')

Whereas:

%d   Dia (00..31)
%e   Dia (0..31)
%m   Mês (00..12)
%y   Ano (dois dígitos)
%Y   Ano (quatro dígitos)
%%   Caractere %
%H   Horas (00..23)
%i   Minutos (00..59)
%s   Segundos (00..59)
%T   Hora completa ( 24 horas, formato hh:mm:ss)

That is, the final sql script was as follows:

SELECT 
   MIN(`TimeString`) as TimeString, 
   AVG(`VarValue`) as VarValue01
FROM `i_l1`
GROUP BY DATE_FORMAT(`TimeString`, 'opcaoDesejava')

Answer 2

I found a way to make it a little bit simpler.

SELECT 
--      pc.hora, /* desabilite caso queira conferir */
CASE WHEN 
    (MINUTE(pc.hora)>=0 AND MINUTE(pc.hora) <=29) THEN 
    `0-29`
ELSE
    `30-59` 
END as minutos,
        HOUR(ja.hora) AS horas,
--      MINUTE(ja.hora) AS minutos,
    YEAR(ja.data) AS ano,
    MONTH(ja.data) AS mes,
    DAYOFMONTH(ja.data) AS dia, 
    count(*) AS acessos
 FROM tabela_acesso as ja 
WHERE 
  ja.data >= ADDDATE(CURDATE(), INTERVAL -1 DAY) 

GROUP BY 
horas, ano, mes, dia
ORDER BY dia, mes, ano, horas, minutos ASC;