Filtrar registros com intervalos de tempo pré-definidos no MySQL, usando PHP

[fjdoliveira]

Pessoal, estou fazendo um sistema de datalogger e gostaria de saber se existe alguma função intrínseca do MySQL para pegar, na tela de pesquisa de registros, o valor em um intervalo de tempo pré-definido, ou se eu devo programar para comparar se o timestamp é igual ou imediatamente superior ao anterior, criar um array e incluir nele os valores com esse tempo pré-definidos. Caso essa situação só se resolva com programação (No meu caso, estou usando PHP), a ideia de fazer um fetch de todos os registros no intervalo completo de interesse e testando a condição do tempo é a forma mais eficaz/elegante de se fazer?

Por exemplo, os registros são armazenados no banco de dados a cada 30 segundos, mas eu queria fazer um gráfico que pegue apenas os dados de hora em hora. Assim, eu pegaria o primeiro registro, em seguida, eu descartaria do gráfico os registros seguintes cujos valores fossem menores do que 1 hora em relação ao primeiro registro filtrado. Em seguida, iria inserir no gráfico o registro cujo valor fosse 1 hora maior do que o segundo registro filtrado, e assim, sucessivamente. Seria como usar o limit/offset, mas ao invés de filtrar pelo número do registro, seria pela data/hora.

Agradeço desde já o apoio

[Leonardo Persan]

Se o campo que registra o tempo é DATETIME e toda hora cheia você tem um registro pode fazer um LIKE:

SELECT * FROM tabela_xpto WHERE campo_xpto LIKE '%00:00';

Caso contrário dependendo da versão do seu MySQL da pra usar GROUP BY e DATE_FORMAT

SELECT * FROM tabela_xpto GROUP BY DATE_FORMAT(campo_xpto, '%Y-%m-%d %H')

[fjdoliveira]

3 horas atrás, Leonardo Persan disse:

Se o campo que registra o tempo é DATETIME e toda hora cheia você tem um registro pode fazer um LIKE:

SELECT * FROM tabela_xpto WHERE campo_xpto LIKE '%00:00';

Caso contrário dependendo da versão do seu MySQL da pra usar GROUP BY e DATE_FORMAT

SELECT * FROM tabela_xpto GROUP BY DATE_FORMAT(campo_xpto, '%Y-%m-%d %H')

Os dados são salvos no banco a cada 30 segundos mas, às vezes ele demora um pouco para salvar e não fica com o valor exato, então acho que a 2a opção é mais interessante, então eu tentei adaptar ao filtro que o usuário deveria selecionar e coloquei "%i" no final para selecionar por minutos:

SELECT * FROM tabela GROUP BY DATE_FORMAT(dataHora, '%Y-%m-%d %H %i') ORDER BY id desc; /* Minuto */

Foi aí que eu comecei a pensar como fazer para que o gráfico exiba os dados a cada 15 minutos, por exemplo. Então, descobri as funções ADDDATE, DATE_ADD e ADDTIME, que têm um parâmetro para definir o intervalo, mas não consigo exibir os dados a cada 15 minutos. Já tentei as opções abaixo, mas só exibe de hora em hora, ou de minuto em minuto, ou exibe todos os registros:

SELECT * FROM tabela GROUP BY DATE_ADD(DATE_FORMAT(dataHora, '%Y-%m-%d %H %i'), INTERVAL 15 MINUTE) ORDER BY id desc; /* Hora */
SELECT * FROM tabela GROUP BY DATE_FORMAT(ADDDATE(dataHora, INTERVAL 15 MINUTE), '%Y-%m-%d %H %i') ORDER BY id desc; /* Minuto */
SELECT * FROM tabela GROUP BY ADDTIME(DATE_FORMAT(dataHora, '%Y-%m-%d %H %i'), "0:15:0") ORDER BY id desc; /* Hora */
SELECT * FROM tabela GROUP BY DATE_FORMAT(ADDTIME(dataHora, "0:15:0"), '%Y-%m-%d %H %i') ORDER BY id desc; /* Minuto */

SELECT * FROM tabela GROUP BY DATE_FORMAT((SELECT ADDDATE(dataHora, INTERVAL 15 MINUTE)), '%Y-%m-%d %H %i') ORDER BY id desc; /* Minuto */
SELECT * FROM tabela GROUP BY (SELECT DATE_ADD(DATE_FORMAT(dataHora, '%Y-%m-%d %H %i'), INTERVAL 15 MINUTE)) ORDER BY id desc; /* Hora */
SELECT * FROM tabela GROUP BY (SELECT ADDDATE(dataHora, INTERVAL 15 MINUTE)) ORDER BY id desc; /* Todos os registros */
SELECT * FROM tabela GROUP BY (SELECT DATE_ADD(dataHora, INTERVAL 15 MINUTE)) ORDER BY id desc; /* Todos os registros */

 

Você poderia me dar uma luz em como poderia fazer para definir os intervalos de tempo informando, através da entrada do usuário, a unidade (Minutos, por exemplo) e a quantidade (No caso, 15, para que ficassem 15 minutos, por exemplo).

Existem 2 campos que o usuário pode usar como filtros de intervalo, o tempo do intervalo (minutos, horas, dias, semanas, meses, etc.) e a quantidade. Assim, se o usuário digitar "15" na quantidade e escolher a opção "minutos", o filtro deverá usar os registros com intervalo de 15 em 15 minutos; se for escolhido "20" e o intervalo "horas", devem ser exibidos os registros a cada 20 horas, e assim sucessivamente.

[Leonardo Persan]

Em 01/11/2019 em 17:45, fjdoliveira disse:

Os dados são salvos no banco a cada 30 segundos

Pensando nisso talvez seja melhor utilizar o WHERE, MINUTE e IN em vez do GROUP BY e DATE_FORMAT, tenta assim:

SELECT * FROM tabela WHERE MINUTE(dataHora) IN(0,15,30,45)

Provavelmente você ainda vai selecionar dois registros por minuto se isso for um problema da pra voltar com o GROUP BY e DATE_FORMAT

SELECT * FROM tabela WHERE MINUTE(dataHora) IN(0,15,30,45) GROUP BY DATE_FORMAT(campo_xpto, '%Y-%m-%d %H:%i')