How to Accurately Calculate Age from a dd-mm-yyyy Date of Birth in MySQL?

Precise Age Calculation from dd-mm-yyyy Date of Birth in MySQL (InnoDB)

This guide explains how to accurately determine age from a date of birth (DOB) stored in dd-mm-yyyy format within a MySQL InnoDB table. Subtracting the DOB from the current date yields a result, but interpreting this result correctly is key for accurate age calculation.

Understanding the Result Format

MySQL's date subtraction returns a timestamp—an integer representing seconds elapsed since the epoch (1970-01-01 00:00:00 UTC). This isn't directly usable for age; further processing is needed.

Calculating Age with TIMESTAMPDIFF()

The TIMESTAMPDIFF() function provides the solution. It takes three arguments:

1. Unit: 'YEAR', 'MONTH', 'DAY', or 'HOUR'.
2. datetime_expr1: The DOB.
3. datetime_expr2: The current date (use CURDATE()).

Example SQL query:

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

This query sets the unit to 'YEAR', uses '1970-02-01' as the DOB, and CURDATE() for the current date, storing the age in the 'age' column. Remember to replace '1970-02-01' with your actual DOB column.

The above is the detailed content of How to Accurately Calculate Age from a dd-mm-yyyy Date of Birth in MySQL?. For more information, please follow other related articles on the PHP Chinese website!