Calculate Age Correctly in MySQL

A person's age is the number of complete calendar years since the date of birth. Subtracting two Unix timestamps does not answer that question reliably: dividing elapsed seconds by 365 × 24 × 60 × 60 ignores leap days and can change the result at the wrong moment around a birthday.

A birth date also describes a calendar day, not an instant in time. Store it as a MySQL DATE rather than as an integer Unix timestamp:

CREATE TABLE person (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    birth_date DATE NOT NULL,
    PRIMARY KEY (id),
    KEY idx_person_birth_date (birth_date)
);

This avoids time-zone conversion, daylight-saving changes, and unnecessary hour-minute-second precision. It also makes the meaning of the value visible in the schema.

Use TIMESTAMPDIFF for Completed Years

MySQL already provides the required calendar operation. With YEAR as its unit, TIMESTAMPDIFF() returns the number of complete years elapsed between the two dates:

SELECT
    name,
    TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age
FROM person;

The argument order matters: MySQL computes the second date minus the first. CURDATE() is evaluated once at the start of the statement, so every row in one result set uses the same reference date. That date follows the MySQL session time zone; applications with a fixed business time zone should configure the session accordingly or pass an explicit reference date.

A synthetic example demonstrates the birthday boundary without relying on live browser time or personal data:

SELECT
    TIMESTAMPDIFF(YEAR, '2000-10-20', '2026-10-19') AS day_before,
    TIMESTAMPDIFF(YEAR, '2000-10-20', '2026-10-20') AS birthday,
    TIMESTAMPDIFF(YEAR, '2000-10-20', '2026-10-21') AS day_after;

+------------+----------+-----------+
| day_before | birthday | day_after |
+------------+----------+-----------+
|         25 |       26 |        26 |
+------------+----------+-----------+

No leap-year correction or average year length is needed because the operation compares calendar dates rather than approximating years with seconds.

Handle Missing and Future Dates

TIMESTAMPDIFF() returns NULL when either date is NULL. A future birth date produces a negative number, which is usually invalid application data. A guarded query can map both cases to NULL:

SELECT
    name,
    CASE
        WHEN birth_date IS NULL OR birth_date > CURDATE() THEN NULL
        ELSE TIMESTAMPDIFF(YEAR, birth_date, CURDATE())
    END AS age
FROM person;

If birth_date is mandatory, retain the NOT NULL constraint and validate that it is not in the future when data enters the application. MySQL CHECK constraints cannot safely encode a permanently moving condition based on the current date, so application validation is the clearer boundary.

A Reusable Function with an Explicit Reference Date

Reports and tests should not depend implicitly on the day they happen to run. Passing the reference date explicitly makes the calculation deterministic and allows historical questions such as a person's age on the date of an event:

DELIMITER //

CREATE FUNCTION age_on(birth_date DATE, as_of_date DATE)
RETURNS SMALLINT UNSIGNED
DETERMINISTIC
NO SQL
RETURN CASE
    WHEN birth_date IS NULL
      OR as_of_date IS NULL
      OR birth_date > as_of_date
    THEN NULL
    ELSE TIMESTAMPDIFF(YEAR, birth_date, as_of_date)
END//

DELIMITER ;

The wider SMALLINT return type avoids imposing the arbitrary 255-year ceiling of TINYINT UNSIGNED. Most queries do not need a stored function at all; the direct built-in expression is simpler and avoids deployment privileges for stored routines.

The February 29 Policy

For a birth date on February 29, MySQL's completed-year calculation reaches the next year on March 1 when the target year has no February 29:

SELECT
    TIMESTAMPDIFF(YEAR, '2000-02-29', '2025-02-28') AS february_28,
    TIMESTAMPDIFF(YEAR, '2000-02-29', '2025-03-01') AS march_1;

+-------------+---------+
| february_28 | march_1 |
+-------------+---------+
|          24 |      25 |
+-------------+---------+

That is a consistent calendar rule, but legal conventions can differ by jurisdiction. If an application must treat February 28 as the anniversary in non-leap years, encode that business rule explicitly and test it separately rather than assuming a generic age function represents a legal definition.

Filtering by Age without Calculating Every Age

For display, calculating an age per returned row is appropriate. For a selective condition such as “at least 18 years old,” avoid wrapping the indexed column in TIMESTAMPDIFF(). Move the calculation to the constant side instead:

SELECT id, name, birth_date
FROM person
WHERE birth_date <= DATE_SUB(CURDATE(), INTERVAL 18 YEAR);

This form gives the optimizer a simple range condition on birth_date, so idx_person_birth_date can be used. The logically equivalent expression below generally requires calculating an age for every candidate row and cannot use the plain birth-date index as a range:

WHERE TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) >= 18

Store the birth date, not the current age. A stored age becomes stale every birthday; deriving it from a stable date keeps one source of truth and lets the same data answer questions for today or any explicit historical reference date.