How to perform cleaning every first Monday of every month? [duplicate]

Question

How to perform cleaning every first Monday of every month? [duplicate]

Navigation

#1 by (2 votes)

2

Hi, I've been working with the mysql event scheduler and everything is fine with the sentences but I really do not have a lot of time with the planner to do the basics and investigate how to do a function that runs only on the first Monday every month at 4:00 but I can not make it so someone could guide me? Thanks!

small example:

mysql> DROP EVENT IF EXISTS 'limpieza';


CREATE EVENT IF NOT EXISTS 'limpieza'
       ON SCHEDULE
         EVERY 1 MONTH
         STARTS '2017-07-06 16:00.00'
         DO
           DELETE FROM 'carrera'
           WHERE 'carrera' = 'Administracion';

mysql

asked by Kevin Duarte 06.07.2017 в 22:46

source

1 answer

add dynamic rows to a table with data from another table Android VOLLEY send json and receive json in response

score 2 · Accepted Answer

For some reason, multiple conditions do not run correctly, within an event.

But I found a way to do what you want.

1) You must create a PROCEDURE and set the weather conditions inside.

DROP PROCEDURE IF EXISTS 'procerure_limpiar';

DELIMITER //

CREATE PROCEDURE procerure_limpiar()
BEGIN   
    SET @fecha := (SELECT fecha FROM nombre_tabla WHERE id_fila = 1);
    SELECT @fecha;    
    IF DATE_FORMAT(@fecha, '%Y %ac %d %H') = DATE_FORMAT(NOW(), '%Y %ac %d %H') 
        THEN DELETE * FROM 'carrera' WHERE'carrera' = 'Administracion';
    END IF;
END; //

DELIMITER ;

Note: With the DATE_FORMAR(NOW(), '%Y %ac %d %H') we are returning 2017-07-07 16 , note that I only get the time and not the minutes or microseconds. To avoid failures in case there is a delay when the PROCEDURE is invoked.

2) Create the EVENT and inside you must invoke the PROCEDURE already created.

DROP EVENT IF EXISTS 'evento_limpiar';

CREATE EVENT 'evento_limpiar' 
  ON SCHEDULE EVERY 1 HOUR
  STARTS '2017-07-06 16:00.00'
DO
  CALL procerure_limpiar();

You can also try the PROCEDURE at any time by just invoking it

CALL procerure_limpiar();

Note: The event is invoked every hour but the DELETE statement will only run on the set date and time.

Update by page of @ wchiquito

This way you do not have to store the date of the event in the database.

DROP EVENT IF EXISTS 'limpieza';

DELIMITER //

CREATE EVENT IF NOT EXISTS 'limpieza'
       ON SCHEDULE
         EVERY 1 HOUR
         STARTS '2017-01-27 00:00.00'
         COMMENT 'EVENTO ejecutado a cada hora. DELETE ejecutado primer lunes mes a las 4:00 PM'
         DO
           BEGIN
             IF (DAYOFMONTH(NOW()) BETWEEN 1 AND 7 AND
                 DAYNAME(NOW()) = 'Monday' AND HOUR(NOW()) = 16) THEN
               DELETE FROM 'carrera'
               WHERE 'carrera' = 'Administracion';
             END IF;
           END//

DELIMITER ;

For more information you can go to your answer .

Note: If we use ON COMPLETION PRESERVE the event will not disappear when executed. Otherwise, if we want it to disappear, we can use ON COMPLETION NOT PRESERVE .

I hope this has been useful to you.