MySQL TIMESTAMPDIFF() Function

 Summary: in this tutorial, you will learn how to use the MySQL TIMESTAMPDIFF() function to calculate the difference between two DATE or DATETIME values.

Introduction to MySQL TIMESTAMPDIFF() function

The TIMESTAMPDIFF() function returns the difference between two datetime expressions in years, months, days, hours, minutes, or seconds.

Here’s the syntax of the TIMESTAMPDIFF function:

TIMESTAMPDIFF(unit, begin, end);Code language: SQL (Structured Query Language) (sql)

In this syntax:

• begin. This is a datetime expression
• end

The TIMESTAMPDIFF function returns the result of begin - end, where begin and end are DATE or DATETIME expressions.

The TIMESTAMPDIFF function allows its arguments to have mixed types e.g., begin is a DATE value and end is a DATETIME value. In case you use a DATE value, the TIMESTAMPDIFF function treats it as a DATETIME value whose time part is '00:00:00'.

The unit argument determines the unit of the result of (end - begin) represented as an integer. The following are valid units:

• MICROSECOND
• SECOND
• MINUTE
• HOUR
• DAY
• WEEK
• MONTH
• QUARTER
• YEAR

MySQL TIMESTAMPDIFF() function examples

Let’s take some examples of using the TIMESTAMPDIFF() function.

1) Simple TIMESTAMPDIFF() function examples

The following example returns a difference between 2010-01-01 and 2010-06-01 in months:

SELECT 
  TIMESTAMPDIFF(
    MONTH, '2010-01-01', '2010-06-01'
  ) result;Code language: SQL (Structured Query Language) (sql)

Output:

+--------+
| result |
+--------+
|      5 |
+--------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

If you want to get the difference in days, you need to change the unit argument from MONTH to DAY as follows:

SELECT 
  TIMESTAMPDIFF(DAY, '2010-01-01', '2010-06-01') result;
Code language: SQL (Structured Query Language) (sql)

Output:

+--------+
| result |
+--------+
|    151 |
+--------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

The following statement returns a difference of two DATETIME values in minutes:

SELECT 
  TIMESTAMPDIFF(
    MINUTE, '2010-01-01 10:00:00', '2010-01-01 10:45:00'
  ) result;
Code language: SQL (Structured Query Language) (sql)

Output:

+--------+
| result |
+--------+
|     45 |
+--------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

Note that the TIMESTAMPDIFF only considers the time part that is relevant to the unit argument. For example:

SELECT 
  TIMESTAMPDIFF(
    MINUTE, '2010-01-01 10:00:00', '2010-01-01 10:45:59'
  ) result;Code language: JavaScript (javascript)

Output:

+--------+
| result |
+--------+
|     45 |
+--------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

The difference should be 45 minutes 59 seconds. However, we use the unit argument as MINUTE, therefore, the function returns only 45 minutes and ignores the 59 seconds.

If you use SECOND instead of MINUTE, then the TIMESTAMPDIFF function will consider the SECOND part as shown in the following example:

SELECT 
  TIMESTAMPDIFF(
    SECOND, '2010-01-01 10:00:00', '2010-01-01 10:45:59'
  ) result;Code language: JavaScript (javascript)

Output:

+--------+
| result |
+--------+
|   2759 |
+--------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

45 minutes 59 second = 45 x 60 + 59 (seconds) = 2759 seconds

2) Calculating ages using the TIMESTAMPDIFF() function

First, create a new table named persons for the demonstration.

CREATE TABLE persons (
    id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(255) NOT NULL,
    date_of_birth DATE NOT NULL
);Code language: SQL (Structured Query Language) (sql)

Second, insert some rows into the persons table:

INSERT INTO persons(full_name, date_of_birth)
VALUES('John Doe', '1990-01-01'),
      ('David Taylor', '1989-06-06'),
      ('Peter Drucker', '1985-03-02'),
      ('Lily Smith', '1992-05-05'),
      ('Mary William', '1995-12-01');Code language: SQL (Structured Query Language) (sql)

Third, use the TIMESTAMPDIFF to calculate the ages of each person in the  persons table:

SELECT 
    id,
    full_name,
    date_of_birth,
    TIMESTAMPDIFF(YEAR,
        date_of_birth,
        '2017-01-01') age
FROM
    persons;Code language: SQL (Structured Query Language) (sql)

Output:

In this statement, we calculated the ages on January 1st 2017.

If you want to calculate the current ages, you can replace the literal value 2017-01-01 by the NOW function as follows:

SELECT 
    id,
    full_name,
    date_of_birth,
    TIMESTAMPDIFF(YEAR,
        date_of_birth,
        NOW()) age
FROM
    persons; Code language: SQL (Structured Query Language) (sql)

Summary

• Use the TIMESTAMPDIFF() function to calculate the difference between two DATE or DATETIME values.