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 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
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);
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.
There are no comments yet. Start the conversation!