Subtract date and display in YEARS, MONTHS and DAYS

Question

SGBD: Mysql

Problem: How to replace the current date with a specific date and return the value in YEARS, MONTHS and DAYS in a query?

Example:

DADOS
DataAdmissao    dataAtual
2010-04-07      2014-06-27 (este dado não está armazenado, seria utilizando a função "NOW()")

RESULTADO
"4 ANOS, 2 MESES e 20 DIAS"

Answer 1

The Problem:

To calculate in years, days and months separately, you need to take some care, because some problems go unnoticed if you do not test with varied dates.

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

Also, when you have two dates in the right order, but with a final month less than initial, and/or a final day less than the initial day, you need to remember to pass on 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 in the proposed solutions:

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

One solution would be to use a series of IFs for that in the SELECT, however, the conditions would have to be calculated correctly for the number of days of each month. It is possible to solve with several IFs, and although complex the result, works well. Fortunately Mysql has several internal functions to handle dates, saving us the boring part.

The Solution:

Mysql has the DATE_SUB(), that already takes into account the days of each month, and to use it correctly, it was enough to extract separately the days, then the months and finally the years of the initial date, represented by CURRENT_DATE or CURDATE().

I avoided the use of NOW() to 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 this SQL Fiddle

Important remarks:

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

  In place of YEAR( @calculo ) - 1,' ANOS, '
  use IF( YEAR( @calculo) > 1, CONCAT( YEAR( @calculo ) - 1,' ANOS, ' ), '' )

  The same logic can be used for plural. 1 ANO, 2 ANOS ...

• If you’re going to use such a thing for labor calculations, for example, you don’t want to solve it in a SELECT. Labor calculation involves a number of extra parameters, and actually the counting has to be done following the peculiarities of the legislation.

• The concept of days, years and months apart is not as absolute as it seems. When you speak of "two years apart", or "three months apart" you may or may not be considering the extra days of leap years, it depends on how this value will be used. You don’t have a solution that fits every case. Always analyze the final application that you will give to the value obtained (it seems strange, but that’s right).

  Example: "one year" before a February 29th is March 1st, or February 28th of the previous year?

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

• No additional protections were added against reversed dates and absurd values just to avoid complicating the answer. Probably these cases and possible errors would be dealt with in the main application anyway.

Answer 2

By making use of Mysql functions for manipulation and/or date collection, we can calculate the different differences in order to obtain the values separately.

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

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

  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 instruction, in this case MONTH to take the months:

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

  ^{The date in question has been calculated and the difference in years has been added so that the number of months is reduced.}

• The same method applies to obtain the days, where we indicate to the function TIMESTAMPDIFF() 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 has been calculated and the difference has been added in months so that the number of days is reduced.}

How do you want to get a sentence, we can then use the function 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

Answer 3

Table Example

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