Knowledgebase

Introduction

When you're creating an age-sensitive application such as a hospital or an insurance company application, you'll encounter situations where you need to compute clients' age based on their date of birth. While it might be easier to store the patients' age in a database table and retrieve those values without further computations, it is not the most optimal solution since the age of patients change with time. A good approach is to store the date of birth in a database table and use the MySQL built-in functions to compute the age dynamically. The company employees can then use the computed values to make better decisions. For instance, since the ability to metabolize medicine changes with age, reporting the patient's exact age to a doctor can help them choose the appropriate dose. In an insurance company, the client's age is one of the variables used to calculate the risk and pricing of the premiums.

In this guide, you'll use the MySQL TIMESTAMPDIFF function to accurately calculate clients' age based on a date of birth (dob) column on Ubuntu 20.04.

Prerequisites

To complete this tutorial, make sure you've all the following:

• An Ubuntu 20.04 server.
• A sudo user.
• A LAMP Stack.

Create a test_db Database

SSH to your server and log in to MySQL as root.

$ sudo mysql -u root -p

Enter your root password for the MySQL server and hit Enter to continue. Next, issue the command below to set up a test_db database.

mysql> CREATE DATABASE test_db;

Select the test_db database.

mysql> USE test_db;

Create a patients table. In this table, you'll store patients' bio-data, including their full names, phone, and date of birth. To uniquely identify each patient, use the patient_id column as the PRIMARY KEY.

mysql> CREATE TABLE patients
       (
           patient_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
           first_name VARCHAR(50),
           last_name VARCHAR(50),
           dob DATE,
           phone VARCHAR(50)
       ) ENGINE = InnoDB;

Once the table is in place, populate it with some records.

mysql> INSERT INTO patients(first_name, last_name, dob, phone) VALUES('JOHN', 'DOE', '1983-09-15', '11111');
mysql> INSERT INTO patients(first_name, last_name, dob, phone) VALUES('MARY', 'SMITH', '1990-04-17', '33333');
mysql> INSERT INTO patients(first_name, last_name, dob, phone) VALUES('BABY', 'ROE', '2021-01-01', '77777');

Make sure you get the confirmation below after running each INSERT statement.

...
Query OK, 1 row affected (0.02 sec)

Retrieve the Age of Patients

In MySQL, you can use the inbuilt TIMESTAMPDIFF() function to compute the difference between two dates and return a value either in days, months or years.

See the basic syntax below.

mysql> SELECT TIMESTAMPDIFF(MONTH, FIRST_DATE, SECOND_DATE);

To retrieve the ages of patients from your patients table, pass the dob as the FIRST_DATE and the current date(CURDATE())) as the SECOND_DATE as shown below.

...
TIMESTAMPDIFF(YEAR, dob, CURDATE()) AS age,
...

Your full MySQL query for retrieving patients' age in years alongside the other bio-data columns should be similar to the syntax below.

mysql> SELECT
       patient_id,
       first_name,
       last_name,
       dob,
       TIMESTAMPDIFF(YEAR, dob, CURDATE()) AS age,
       phone
       FROM patients;

Once you run the query, you should get the output shown below.

+------------+------------+-----------+------------+------+-------+
| patient_id | first_name | last_name | dob        | age  | phone |
+------------+------------+-----------+------------+------+-------+
|          1 | JOHN       | DOE       | 1983-09-15 |   37 | 11111 |
|          2 | MARY       | SMITH     | 1990-04-17 |   30 | 33333 |
|          3 | BABY       | ROE       | 2021-01-01 |    0 | 77777 |
+------------+------------+-----------+------------+------+-------+
3 rows in set (0.00 sec)

While the MySQL query above looks great, it displays 0 output for patients with less than one year(BABY ROE). This is because the TIMESTAMPDIFF only returns the number of complete periods (in this case, years) between two dates while taking into account the varying days in each month(28, 29, 30, 31) and leap years (either 365 or 366 days).

The output might mislead health and insurance records because clients younger than one year old are reported as having 0 years.

To overcome this challenge, use a combination of MySQL functions to compute the exact age. Run the query below.

 mysql> SELECT
        patient_id,
        first_name,
        last_name,
        dob,
        CONCAT
        (
            FLOOR((TIMESTAMPDIFF(MONTH, dob, CURDATE()) / 12)), ' YEARS ',
            MOD(TIMESTAMPDIFF(MONTH, dob, CURDATE()), 12) , ' MONTHS'
        ) AS age,
        phone
        FROM patients;

In the syntax above, you're using the MySQL TIMESTAMPDIFF function to compute the number of complete months between the patients' date of birth (dob) and the current date (CURDATE()). Then, you're using the MySQL FLOOR function to get only the full months without any decimals. To retrieve the years, you're dividing the months by 12.

Next, you're using the MySQL MOD function to retrieve the remaining months that don't add up to one year. Then, you're using the MySQL CONCAT statement to combine the resulting value into one string.

After you run the above query, you should now get the patients' age showing their exact age in years and months.

 +------------+------------+-----------+------------+--------------------+-------+
 | patient_id | first_name | last_name | dob        | age                | phone |
 +------------+------------+-----------+------------+--------------------+-------+
 |          1 | JOHN       | DOE       | 1983-09-15 | 37 YEARS 5 MONTHS  | 11111 |
 |          2 | MARY       | SMITH     | 1990-04-17 | 30 YEARS 10 MONTHS | 33333 |
 |          3 | BABY       | ROE       | 2021-01-01 | 0 YEARS 2 MONTHS   | 77777 |
 +------------+------------+-----------+------------+--------------------+-------+
 3 rows in set (0.00 sec)

Use the computed age above when displaying the clients' bio-data in your frontend mobile or desktop application to allow end-user to make valid decisions based on the computed age.

Conclusion

In this guide, you've set up a sample database and used the MySQL built-in functions including TIMESTAMPDIFF, CONCAT, FLOOR, and MOD to dynamically compute the ages of patients based on their date of birth.