How to Get the number of days, months, and years between an employee's date of birth and today&-CodePudding

I tried this but how to calculate for leap years.

SELECT
  FLOOR(DATEDIFF(NOW(), date_of_birth) / 365.25) as years,
  FLOOR(MOD(DATEDIFF(NOW(), date_of_birth), 365.25) / 30.4375) as months,
  MOD(DATEDIFF(NOW(), date_of_birth), 30.4375) as days
FROM employees;

Can anyone please help

CodePudding user response：

For example:

SELECT @years := TIMESTAMPDIFF(YEAR, date_of_birth, CURRENT_DATE) years,
       @months := TIMESTAMPDIFF(MONTH, date_of_birth   INTERVAL @years YEAR, CURRENT_DATE) months,
       TIMESTAMPDIFF(MONTH, date_of_birth   INTERVAL @years YEAR   INTERVAL @months MONTH, CURRENT_DATE) days
FROM employees;

Of course you may use according expressions instead of UDVs.

But there is a problem. The number of days per month vary, so the days amount in years-months-days may differ depends on the calculation direction. My query adds previously calculated years and months to date_of_birth, you may create similar query which substracts years and months from current date.. and the days amount may differ.

CodePudding user response：

mysql> insert into employees set name = 'Harry Potter', date_of_birth = '1980-07-31';

mysql> SELECT
  TIMESTAMPDIFF(YEAR, date_of_birth, NOW()) AS years,
  TIMESTAMPDIFF(MONTH, date_of_birth, NOW()) AS months,
  TIMESTAMPDIFF(DAY, date_of_birth, NOW()) AS days
FROM employees;

 ------- -------- ------- 
| years | months | days  |
 ------- -------- ------- 
|    42 |    509 | 15523 |
 ------- -------- -------

See https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_timestampdiff

Re your comment:

If MySQL date functions didn't handle leap years, they'd be pretty useless, right? In fact, they do know how to handle leap years.

mysql> select datediff(now(),'1980-01-30') as diff_with_leap_years;
 ---------------------- 
| diff_with_leap_years |
 ---------------------- 
|                15706 |
 ---------------------- 

mysql> select 365*43 as diff_without_leap_years;
 ------------------------- 
| diff_without_leap_years |
 ------------------------- 
|                   15695 |
 -------------------------

Thus there are 11 leap years between 1980 and today (I am writing this on 2023-01-30).