How to Calculate Time Difference with MySQL

In this tutorial, you'll learn how to calculate the difference between two date and time strings in MySQL with the TIMESTAMPDIFF() method.

The Syntax

MySQL's TIMESTAMPDIFF() method is defined with the following syntax, accepting three required parameters:

SELECT TIMESTAMPDIFF(unit, date_time_1, date_time_2);

The parameters are:

• unit - The unit of the date and time measurement in seconds, minutes, hours, days, months, years, etc.
• date_time_1 & date_time_2 - The date and time strings in Y-m-d H:i:s format.

Code Examples

This query calculates the number of seconds between two timestamps, "2023-02-17 10:00:00" and "2023-02-17 11:30:00", resulting in 5400 seconds (or 1.5 hours):

SELECT TIMESTAMPDIFF(SECOND, '2023-02-17 10:00:00', '2023-02-17 11:30:00');

You could also calculate the difference in minutes between the same hard-coded timestamps, which returns a result of 90 minutes:

SELECT TIMESTAMPDIFF(MINUTE, '2023-02-17 10:00:00', '2023-02-17 11:30:00');

You can also run calculations between two variables with timestamp values:

SET @date1 = '2023-02-17 10:00:00';
SET @date2 = '2023-02-17 11:30:00';

SELECT TIMESTAMPDIFF(SECOND, @date1, @date2);

Conclusion

This tutorial showed you how to calculate the difference between two timestamps with different units of time measurement, with both hard-coded and variable-assigned values.