How to Calculate Age from a Date of Birth Using MySQL?

Use MySQL (InnoDB) to calculate age

This article describes how to use MySQL's TIMESTAMPDIFF function to calculate age based on the date of birth stored in the database.

Question:

Assuming the date of birth is stored in 'dd-mm-yyyy' format, we need to determine:

• The format of the date returned after subtracting the dates.
• How to calculate a person's age using the returned format.

Solution:

Date format:

The date format returned by MySQL after subtracting another date from the current date is the default MySQL date and time format: YYYY-MM-DD HH:MM:SS.

Calculate age:

We can use the TIMESTAMPDIFF function to calculate the difference between two dates in different units (including years). An example is as follows:

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

This query calculates the difference between February 1, 1970 and the current date and returns the age in years.

Example:

Consider a table containing a column called 'date_of_birth' in the format 'dd-mm-yyyy':

<code class="language-sql">CREATE TABLE persons (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  date_of_birth DATE
);

INSERT INTO persons (name, date_of_birth) VALUES
  ('John Doe', '1970-02-01'),
  ('Jane Doe', '1975-04-15');</code>

To calculate 'John Doe's' age we will run the following query:

<code class="language-sql">SELECT TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) AS age
FROM persons
WHERE name = 'John Doe';</code>

This query will return results:

<code>age
51</code>

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