Calculating Age Difference in Years as an Integer in MySQL

Determining a person's age in a database presents a slight challenge when dealing with age calculations. Consider the 'student' table with columns 'id' and 'birth_date'.

To calculate age in days, the expression datediff(curdate(),birth_date) can be used, but this returns a floating-point value. Dividing by 365 yields a floating-point value as well.

Calculating years as year(curdate())-year(birth_date) also introduces a problem. For example, if a person was born in June 1970 and the current date is in May, the expression would incorrectly return 32 years instead of 31.

Alternative Solution for Integer Year Difference:

To address this issue, consider the following SQL statement:

SELECT TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) AS difference FROM student

This expression calculates the difference between the 'date_of_birth' and 'CURDATE()' in years, resulting in an integer value.

Extension for Months and Days:

To calculate the difference in months, replace 'YEAR' with 'MONTH' in the expression:

SELECT TIMESTAMPDIFF(MONTH, date_of_birth, CURDATE()) AS difference FROM student

Similarly, for the difference in days, replace 'YEAR' with 'DAY':

SELECT TIMESTAMPDIFF(DAY, date_of_birth, CURDATE()) AS difference FROM student