MySQL Forums
Forum List  »  Source, Builds, Binaries

DATE_ADD + DATE_FORMAT
Posted by: Alejandro Jose Goncalves Correia
Date: June 14, 2023 03:45AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
DATE_ADD + DATE_FORMAT
279
June 14, 2023 03:45AM
89
June 15, 2023 04:35AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.