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

Question

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

Answer 1

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).

Answer 2

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.