Subtract date and display in YEARS, MONTHS and DAYS

Example Table

CREATE TABLE tablexemplo(
    DataAdmissao date
  );

INSERT INTO tablexemplo(DataAdmissao) values('2010-04-07');
INSERT INTO tablexemplo(DataAdmissao) values('2010-04-08');
INSERT INTO tablexemplo(DataAdmissao) values('2010-04-09');
INSERT INTO tablexemplo(DataAdmissao) values('1980-06-28');
INSERT INTO tablexemplo(DataAdmissao) values('2010-06-30');
INSERT INTO tablexemplo(DataAdmissao) values('2010-06-28');
INSERT INTO tablexemplo(DataAdmissao) values(current_date);

SQL

SELECT 
   date_format(DataAdmissao,'%d/%m/%Y') as DataAdmissao, 
   date_format(current_date,'%d/%m/%Y') as DataHoje, 
   Anos, 
   Mes, 
   Dias, 
   Concat(Anos, ' Anos ', Mes, ' Mes e ', Dias, ' Dias') AS Extenso
FROM (
    SELECT  
        DataAdmissao,
        TIMESTAMPDIFF(YEAR, DataAdmissao, current_date) as Anos,
        TIMESTAMPDIFF(MONTH, DataAdmissao + INTERVAL TIMESTAMPDIFF(YEAR,  DataAdmissao, current_date) YEAR , current_date) AS Mes,
        TIMESTAMPDIFF(DAY, DataAdmissao + INTERVAL TIMESTAMPDIFF(MONTH,  DataAdmissao, current_date) MONTH , current_date) AS Dias
    FROM tablexemplo
) AS T

Example: SQLFiddle

The Problem:

In order to calculate in years, days and months separately, you need to take some care, as some problems go unnoticed if you do not test with different dates.

Calculations based on day difference give problems by the difference of days in each month, and end up returning some strange things when converting to YYYY MM DD, such as rounding errors and broken numbers.

In addition, when you have two dates in the right order, but with a final month less than initial, and / or final day less than the initial day, you need to remember to pass negative days and months to the months and years of the calculation, and add the days of the month in the right amount. These two dates show the problem well, test them on the proposed solutions:

Data inicial | Data final 
-------------+-------------
2013-09-30   | 2014-06-28  

One solution would be to use a series of IF s for this in SELECT , however, it would have to calculate correctly the conditions for the number of days of each month. It can be resolved with multiple% s of% s, and although complex the result, works well. Fortunately MySQL has several built-in functions to handle dates, sparing you from the boring part.

The Solution:

MySQL has IF , which already takes into account the days of each month, and to use it correctly, it was enough to extract the days, then the months and finally the years of the initial date, represented by DATE_SUB() or% with%.

I avoided using CURRENT_DATE so I did not have to deal with hours, minutes, and seconds.

Follow the query:

SELECT
   @calculo :=
      DATE_SUB( DATE_SUB( DATE_SUB( CURRENT_DATE,
               INTERVAL YEAR( DataDeAdmissao ) - 1 YEAR
            ), INTERVAL MONTH( DataDeAdmissao ) - 1 MONTH
            ), INTERVAL DAY( DataDeAdmissao ) - 1 DAY
      ) AS Calculo,

   DataDeAdmissao,

   CURRENT_DATE AS Hoje,

   CONCAT(
         YEAR( @calculo ) - 1,' ANOS, ',
         MONTH( @calculo ) - 1,' MESES E ',
         DAY( @calculo ) - 1,' DIAS'
      ) AS Extenso

   FROM TabelaExemplo

  

Test the operation in SQL Fiddle

Important notes:

• To not show "0 YEARS", "0 MONTHS" or "0 DAYS", just use the following setting:

  Instead of CURDATE()
  use NOW()

  The same logic can be used for the plural. YEAR( @calculo ) - 1,' ANOS, ' , IF( YEAR( @calculo) > 1, CONCAT( YEAR( @calculo ) - 1,' ANOS, ' ), '' ) ...

• If you are going to use such a thing for work calculations, for example, do not want to solve this in a SELECT. Labor calculation involves a number of extra parameters, and actually counting has to be done following the peculiarities of the legislation.

• The concept of difference of days, years and months is not as absolute as it seems. When you talk about "two years of difference," or "three months of difference" you may or may not be considering the extra leap years, it depends how this value will be used. It does not have a solution that works for every case. Always look at the final application you are going to give to the value (it looks strange, but that's right).

  Example: "a year" before a February 29 is the first of March, or February 28 of the previous year?

• Still based on the previous point: remember that you can reverse the order of subtractions in the select above , to first deal with years, months and days. Depending on the criteria you want, this can make a difference in critical dates and extreme cases.

• No additional protections against reversed dates and absurd values were added just to not complicate the response. Probably these cases and possible errors would be handled in the main application anyway.

Using the MySQL functions for manipulation and / or collection of dates, we can calculate the different form differences to get the values separately.

Assuming the value of the DataAdmission column is "2010-04-07" and the current date is "2014-06-29":

• With the function YEAR() we got extract the year from a date. With CURDATE() we can get the date current:

     

  SELECT YEAR(CURDATE()) - YEAR(DataAdmissao)
  
  -- Resultado:
  -- 4
  

• With the function TIMESTAMPDIFF() we can calculate the difference between two dates, obtaining the result according to the first statement, in this case MONTH to stay with the months:

     

  SELECT TIMESTAMPDIFF(
           MONTH,
           DataAdmissao + INTERVAL TIMESTAMPDIFF(YEAR, DataAdmissao, CURDATE()) YEAR,
           CURDATE()
         )
  
  -- Resultado:
  -- 2
  

  The date in question was calculated and the difference was added in years so that the number of months is reduced.

• The same method applies to get the days, where we indicate to the function TIMESTAMPDIFF() that we want the result in days:

     

  SELECT TIMESTAMPDIFF(
            DAY,
            "2010-04-07" + INTERVAL TIMESTAMPDIFF(MONTH, "2010-04-07", CURDATE()) MONTH,
            CURDATE()
          )
  
  -- Resultado:
  -- 22
  

  The date in question was calculated and the difference was added in months for the number of days to be reduced

How do you want to get a phrase, then we can use CONCAT() to put it all together:

SELECT
  CONCAT (
    YEAR(NOW()) - YEAR(DataAdmissao),
    " anos, ",
    TIMESTAMPDIFF(
      MONTH,
      DataAdmissao + INTERVAL TIMESTAMPDIFF(YEAR, DataAdmissao, CURDATE()) YEAR,
      CURDATE()
    ),
    " meses e ",
    TIMESTAMPDIFF(
      DAY,
      DataAdmissao + INTERVAL TIMESTAMPDIFF(MONTH, DataAdmissao, CURDATE()) MONTH,
      CURDATE()
    ),
    " dias."
  ) as meuTexto

-- Resultado:
-- 4 anos, 2 meses e 22 dias.

SQL Fiddle