NovaRoma-5min.sql

SET time_zone = '-3:00'; #Resultados em horário normal.
SELECT #TIMESTAMP,
'311' as COD,
YEAR(Timestamp) as Ano,
dayofyear(Timestamp) as DJ,
#HOUR(Timestamp) as Hora,
#MINUTE(Timestamp) as Minuto,
Concat(
            HOUR(timestamp), 
            CASE
               WHEN MINUTE(timestamp) BETWEEN 0 AND 4 THEN '00'
               WHEN MINUTE(timestamp) BETWEEN 5 AND 9 THEN '05'
               WHEN MINUTE(timestamp) BETWEEN 10 AND 14 THEN '10'
               WHEN MINUTE(timestamp) BETWEEN 15 AND 19 THEN '15'
               WHEN MINUTE(timestamp) BETWEEN 20 AND 24 THEN '20'
               WHEN MINUTE(timestamp) BETWEEN 25 AND 29 THEN '25'
               WHEN MINUTE(timestamp) BETWEEN 30 AND 34 THEN '30'
               WHEN MINUTE(timestamp) BETWEEN 35 AND 39 THEN '35'
               WHEN MINUTE(timestamp) BETWEEN 40 AND 44 THEN '40'
               WHEN MINUTE(timestamp) BETWEEN 45 AND 49 THEN '45'
               WHEN MINUTE(timestamp) BETWEEN 50 AND 54 THEN '50'
               WHEN MINUTE(timestamp) BETWEEN 55 AND 59 THEN '55'
            END
) as hhmm,
SECOND(Timestamp) as Segundos,
ROUND(AVG(WS_ms_S_WVT),3) as WS_ms_S_WVT,
ROUND((WindDir_D1_WVT),3) as WindDir_D1_WVT,
ROUND((WindDir_SD1_WVT),3) as WindDir_SD1_WVT,
ROUND(AVG(WS_msa),3) as WS_msa,
ROUND((WindDirs),3) as WindDirs,
#ROUND(AVG(WindDirs),3) as WindDirs,
#90-(ATAN2( (avg(cos(WindDirs))/avg(sin(WindDirs))) )*180./3.1456) as WindDirs,
ROUND(AVG(SlrW),3) as SlrW,
ROUND(AVG(AirTC),3) as AirTC,
ROUND(AVG(RH),3) as RH,
'7999' as T108_C, #ROUND(AVG(T108_C),2) as T108_C,
ROUND(AVG(BP_mbar),3) as BP_mbar,
ROUND(AVG(Battery),3) as Battery,
ROUND(AVG(WS_mssd),3) as WS_mssd,
ROUND(AVG(AirTCsd),3) as AirTCsd,
ROUND(AVG(WS_msmax),3) as WS_msmax,
ROUND(AVG(AirTCmax),3) as AirTCmax,
ROUND(AVG(WS_msmin),3) as WS_msmin,
ROUND(AVG(AirTCmin),3) as AirTCmin ,
ROUND(SUM(Rain_mm),3) as Rain_mm 
   FROM sr311
    WHERE timestamp >= '2015-03-01' #AND timestamp <= '2011-12-05'
GROUP BY  CONCAT( 
           DATE(timestamp), ' ',
            HOUR(timestamp), ':',
            CASE
               WHEN MINUTE(timestamp) BETWEEN 0 AND 4 THEN '00'
               WHEN MINUTE(timestamp) BETWEEN 5 AND 9 THEN '05'
               WHEN MINUTE(timestamp) BETWEEN 10 AND 14 THEN '10'
               WHEN MINUTE(timestamp) BETWEEN 15 AND 19 THEN '15'
               WHEN MINUTE(timestamp) BETWEEN 20 AND 24 THEN '20'
               WHEN MINUTE(timestamp) BETWEEN 25 AND 29 THEN '25'
               WHEN MINUTE(timestamp) BETWEEN 30 AND 34 THEN '30'
               WHEN MINUTE(timestamp) BETWEEN 35 AND 39 THEN '35'
               WHEN MINUTE(timestamp) BETWEEN 40 AND 44 THEN '40'
               WHEN MINUTE(timestamp) BETWEEN 45 AND 49 THEN '45'
               WHEN MINUTE(timestamp) BETWEEN 50 AND 54 THEN '50'
               WHEN MINUTE(timestamp) BETWEEN 55 AND 59 THEN '55'
            END
       )
  ORDER BY TIMESTAMP ASC 
  LIMIT 0,300 
;