DATE_ADD + DATE_FORMAT
Hello,
I have a code already made in Indusoft that executes a query in a mysql DB, it takes the values from the database and organizes them, finally these values are shown in an indusoft graph.
Every certain time a radiation record is stored in the database, every 6 minutes I think, of 3 radiation sensors with the date of the moment, the query that is done takes all the records of an hour, groups and averages them for that same hour, for example: 10:00, 10:06, 10:12, 10:18, 10:24, 10:30, 10:36, 10:42, 10:48, 10:54; groups them for the hour of 10:00, and then so for each hour. What I want is that instead of taking the records this way it does it from behind, for example: 09:06, 09:12, 09:18, 09:24, 09:30, 09:36, 09:42, 09:48, 09:54, 10:00; it groups them for the 10:00 time, since this is a correct standard.
The original code is as follows:
SELECT DATE_FORMAT(dm.fecha_lectura, '%m/%d/%Y %H:00:00.000') AS FECHA, round((avg(radiacion_1) + avg(radiacion_2) + avg(radiacion_3))/3,0) AS RADIACION FROM avanzalia.datos_meteo dm WHERE dm.nombre_planta = '" & plantaMeteo & "' and DATE_FORMAT(dm.fecha_lectura,'%Y-%m-%d %H:%i:%s.0') >= date_format(NOW() - INTERVAL 1 day,'%Y-%m-%d %07') and DATE_FORMAT(dm.fecha_lectura,'%H') not in (00,01,02,03,04,05,06) group by DATE_FORMAT(dm.fecha_lectura, '%m/%d/%Y %H:00:00.000') ORDER BY dm.fecha_lectura
I tried to modify the code unsuccessfully in this way, I put a DATE_ADD so that it would start taking the records 54 minutes earlier:
SELECT DATE_FORMAT(dm.fecha_lectura(DATE_ADD('%m/%d/%Y %H:00:00.000',INTERVAL -54 MINUTE), '%m/%d/%Y %H:00:00.000') AS FECHA, round((avg(radiacion_1) + avg(radiacion_2) + avg(radiacion_3))/3,0) AS RADIACION FROM avanzalia.datos_meteo dm WHERE dm.nombre_planta = '" & plantaMeteo & "' and DATE_FORMAT(dm.fecha_lectura(DATE_ADD('%m/%d/%Y %H:00:00.000',INTERVAL -54 MINUTE), '%m/%d/%Y %H:00:00.000') >= date_format(NOW() - INTERVAL 1 day,'%Y-%m-%d %07') and DATE_FORMAT(dm.fecha_lectura,'%H') not in (00,01,02,03,04,05,06) group by DATE_FORMAT(dm.fecha_lectura(DATE_ADD('%m/%d/%Y %H:00:00.000',INTERVAL -54 MINUTE), '%m/%d/%Y %H:00:00.000') ORDER BY dm.fecha_lectura