How to Calculate Age from a Date of Birth Using MySQL's TIMESTAMPDIFF()?

Determining Age in MySQL (InnoDB) Databases

Let's say you have a database table storing individual birthdates in "dd-mm-yyyy" format. Calculating age involves subtracting the birthdate from the current date, but this yields a date difference, not a readily usable age.

To get the age in years, employ the TIMESTAMPDIFF() function. This function computes the difference between two datetime expressions, providing the result in a chosen unit.

Function Syntax:

<code class="language-sql">TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2)</code>

Here's how to apply it to calculate age:

<code class="language-sql">SELECT TIMESTAMPDIFF(YEAR, '1970-02-01', CURDATE()) AS age;</code>

This query subtracts the birthdate ('1970-02-01') from the current date (CURDATE()), returning the difference in years.

Illustrative Example:

With a birthdate of '1970-02-01' and the current date as '2023-02-01':

<code>TIMESTAMPDIFF(YEAR, '1970-02-01', '2023-02-01') = 53</code>

The calculated age is 53 years.

Important Consideration:

Ensure your birthdate column is a correctly formatted datetime or date type; TIMESTAMPDIFF() requires compatible data types.

The above is the detailed content of How to Calculate Age from a Date of Birth Using MySQL's TIMESTAMPDIFF()?. For more information, please follow other related articles on the PHP Chinese website!