Calculate difference between dates and change status

Question

I need to create some routine to calculate the difference between two dates directly in the Mysql database and change the status of another field according to this difference. The database fields are :

1. Date Calibration,
2. Calibration Expiration,
3. Calibration status : Up to date, Calibrate, Unsuccessful.

How do I create the routine that takes the two dates make the difference calculation and when the date is more than 30 days the status is "on time", when it is less than 30 days the status is "Calibrate" and when the date is greater than the field "calibration maturity" the status is "Expired" ?

Answer 1

I will not answer you how to do this in the database, I will propose you a way that (in my opinion) is more suitable for calculated values. Where these calculated fields would not be fields saved in the database but rather calculated based on their indicators.

Upside:

• The field will be recalculated for each new request, based on the current state of the object;
• You do not need Procedures, Functions, or Triggers for this task linked to databases;
• You do not run the risk of the calculated field becoming outdated based on your indicators;
• Among other numerous minor advantages it is possible to point out easily;

Disadvantages:

• If the bank is used by more than one system, all those involved must incorporate the rule to calculate the calculated fields;
• If you do not use Classes template (Assigns the query cursor directly to Datasource (particularly not recommend));

Implementation

It would be something similar to this in C# (which seems to be the language you are using):

public class Calibragem{
    public long Id { get; set; }
    public DateTime Data { get; set; }
    public DateTime Vencimento { get; set; }
    public StatusCalibragem Status { 
        get{
            // aqui você faz a conta da diferença em mémoria
            /** 
             * DUVIDA: aqui você não deveria calcula a Data da ultima calibragem com a 
             * data atual(DateTime.Now) ou o Vencimento com a data atual(DateTime.Now),
             * ao invés de fazer o calculo entre as duas Datas?
            **/    
            TimeSpan diff = Data - Vencimento;

            // verifica a diferença em dias para ver qual o status atual
            if(diff.Days > 30){
                return StatusCalibragem.EmDia;
            }
            if(diff.Days < 30){
                return StatusCalibragem.Calibrar;
            }
            // não entendi quando será Vencida, com sua explicação
        } 
        private set; 
    }
}

public enum StatusCalibragem
{
    EmDia,
    Calibrar,
    Vencida
}

In the database your table would only have columns:

ID | DATA_CALIBRAGEM | VENCIMENTO_CALIBRAGEM

Answer 2

To update the column according to the date conditions you can use something like this:

UPDATE suatabela
   SET status = CASE WHEN CURDATE > data_vencimento THEN
                   status1
                ELSE
                   CASE WHEN DATEDIFF(CURDATE(), data_calibragem) > 30 THEN
                      status2
                   ELSE
                      status3
                   END   
                END;

Remember that with this instruction all records in the table will be affected. For at some point, each one will be in one of the 3 situations cited by you.

Remarks:

• CURDATE returns the current day (https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_curdate).
• DATEDIFF returns the difference between two dates (https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_datediff).

In order for this instruction to be executed daily you must create a Event in the database (http://dev.mysql.com/doc/refman/5.7/en/events-overview.html).

An example:

delimiter |

CREATE EVENT e_upd_calibragem
    ON SCHEDULE
      EVERY 1 DAY
    COMMENT 'Atualiza status de calibragem'
    DO
      BEGIN

        UPDATE suatabela
           SET status = CASE WHEN CURDATE > data_vencimento THEN
                           status1
                        ELSE
                           CASE WHEN DATEDIFF(CURDATE(), data_calibragem) > 30 THEN
                              status2
                           ELSE
                              status3
                           END   
                        END;

      END |

delimiter ;