How to Calculate Age Difference in Years as an Integer in MySQL?

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:

<code class="sql">SELECT TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) AS difference FROM student</code>

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:

<code class="sql">SELECT TIMESTAMPDIFF(MONTH, date_of_birth, CURDATE()) AS difference FROM student</code>

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

<code class="sql">SELECT TIMESTAMPDIFF(DAY, date_of_birth, CURDATE()) AS difference FROM student</code>

The above is the detailed content of How to Calculate Age Difference in Years as an Integer in MySQL?. For more information, please follow other related articles on the PHP Chinese website!