What is the meaning of INTERVAL '0-0' YEAR_MONTH on MySQL?

user_5021320 0
Viewed 285

I am trying to understand a MySQL stored procedure but I don't understand the use of YEAR_MONTH. What it means INTERVAL '0-0' YEAR_MONTH and INTERVAL '0-4' YEAR_MONTH? What it is used for?

The query on the stored procedure is like this:

SELECT remunerations.id_princ,
       remunerations.id_cont,
       customer.t_contraco,
       remunerations.id_ct,
       ABS(SUM(remunerations.item1)) AS item1,
       ABS(SUM(remunerations.item2)) AS item2,
       ABS(SUM(remunerations.item3)) AS item3
FROM customer
     RIGHT OUTER JOIN remunerations ON (customer.id_cont = remunerations.id_cont)
     RIGHT OUTER JOIN contractuals ON (remunerations.id_ct = contractuals.id_ct)
WHERE remunerations.id_princ = var_princ AND
      customer.active_cont = 1 AND
      customer.deactive_princ = 1 AND
      DATE_FORMAT(CONCAT(remunerations.anyo_trab, '-', remunerations.mes_trab, '-15'), "%Y-%m-%d") IN ((
              SELECT DATE_SUB(CONCAT(MAX(rm.anyo_trab),'-', MAX(rm.mes_trab),'-15'), INTERVAL '0-0' YEAR_MONTH) AS fecha2
              FROM
                remunerations rm
              WHERE rm.anyo_trab = YEAR (date_sub (NOW(), INTERVAL '0-4' YEAR_MONTH)) AND
                    rm.id_princ = var_princ AND
                    rm.id_cont = remunerations.id_cont
      )) AND
      (remunerations.finiquito_planilla IS NULL OR remunerations.finiquito_planilla >= var_fecha)
GROUP BY remunerations.id_princ,
         remunerations.id_cont,
         customer.elorden,
         remunerations.id_ct;
yearmonth mysql
3 Answers

That is an argument to DATE_SUB. Looking at the documentation for that, it says:

See the description for DATE_ADD().

And the documentation there says:

DATE_ADD(date,INTERVAL expr unit), DATE_SUB(date,INTERVAL expr unit)

Your unit is YEAR_MONTH, so according to the linked Temporal Intervals, the expression is a number of years and months to subtract, separated by a '-'.

ysth_173890

It seems it is used to cast value to date data type:

SELECT DATE_SUB(CONCAT(MAX(rm.anyo_trab),'-', MAX(rm.mes_trab),'-15'), 
            INTERVAL '0-0' YEAR_MONTH) AS fecha2

As

CONCAT(MAX(rm.anyo_trab),'-', MAX(rm.mes_trab),'-15'

is definitely a text. Adding 0 literaly does not change the value except implicit conversion. Effectively it is the same as:

SELECT CAST(CONCAT(MAX(rm.anyo_trab),'-', MAX(rm.mes_trab),'-15') AS DATE) ...

db<>fiddle demo

user_50708790

Basically the DATE_SUB() function is used in SQL to subtract a time/date interval from a date and then returns the date.

so this date_sub (NOW(), INTERVAL '0-4' YEAR_MONTH) means that

date_sub - function to subtract    
NOW() -  would be the current DATE and TIME
INTERVAL - value to be added or subtracted
'0-4' - corresponds to YEAR=0 & MONTH=4

So you can read the expression as subtract 4 months from Current date and time .

You can check this in a simple Select Query

Addionally,also please check this official Doscumentation https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-add

user_63091110
Related