Skip to Content

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.

Ad - Web Hosting from SiteGround - Crafted for easy site management. Click to learn more.
Advertising Disclosure: I am compensated for purchases made through affiliate links. Click here for details.

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.

Posted by: Josh Rowe
Created: February 17, 2023

Comments

There are no comments yet. Start the conversation!

Add A Comment

Comment Etiquette: Wrap code in a <code> and </code>. Please keep comments on-topic, do not post spam, keep the conversation constructive, and be nice to each other.