Select with averages in minutes - Mysql

Navigation
0

Below is a query returned in a table from my database (MySQL):

WhatinterestsmearetheTimeString,VarValue,andidcolumns.Every5secondsonaverageaninsertismadetomysystem.IhaveDateTimevaluesof1month.

Doubt:

HowdoIsetupqueriesthatreturnmeatintervalsof1minute,15minutes,30minutes,1hour,1day,theVarValueaverage?Itcanbereferencedseparately,wheretheuserwillselecttherange.Thatis,ifitselectsintervalsevery1hour,itshouldlooklikethis:

TimeString|VarValue

2017-10-0623:50:50|30.55

2017-10-0600:50:55|31.55

2017-10-0601:50:55|41.55...

Itrieddoingthefollowing:

SELECTMIN('i_l1'.'TimeString')asTimeString,AVG('i_l1'.'VarValue')asVarValue01FROM'i_l1'GROUPBYHOUR('i_l1'.'TimeString')

Queryreturn:

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

Comment

In SQL Server I had done the following, 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

It could be something similar to the example above.

    
asked by anonymous 01.11.2017 / 12:25

1 answer

0

I was able to resolve using GROUP BY, follows ( OptionDoes ): 

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')

Being that: 

%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 looks like this: 

SELECT 
   MIN('TimeString') as TimeString, 
   AVG('VarValue') as VarValue01
FROM 'i_l1'
GROUP BY DATE_FORMAT('TimeString', 'opcaoDesejava')
    
01.11.2017 / 14:33
Get link from an image saved in bd Fibonacci in Java with recursion [duplicate]