MySQL TIMEDIFF()

The TIMEDIFF() function used in SQL allows to calculate the difference between 2 distinct times. The two arguments of the function can either be hours (TIME) or dates with time (DATETIME), the result will be a time between “-838:59:59” and “838:59:59”.
 

Syntax:

The following syntax can be used in a SQL query:

SELECT TIMEDIFF(hour1, hour2);

 

 

Example :

Here are some typical examples:

SELECT TIMEDIFF("HH:MM", "HH:MM");

Format of the output:

HH:MM

---

SELECT TIMEDIFF("HH:MM:SS", "HH:MM:SS");

Format of the output:

HH:MM:SS

---

SELECT TIMEDIFF("YYYY-MM-DD HH:MM", "YYYY-MM-DD HH:MM");

Format of the output:

HH:MM

---

SELECT TIMEDIFF("YYYY-MM-DD HH:MM:SS", "YYYY-MM-DD HH:MM:SS");

Format of the output:

HH:MM:SS

 

 

Example of date and time comparisons

This date/time function is useful to compare 2 different times. An application may need such a comparison to estimate the response time between 2 events.

Here are some practical examples of uses, including the results:

SELECT TIMEDIFF("09:12:14", "09:11:13"); -- output: 00:01:01
SELECT TIMEDIFF("09:10:11", "09:11:12"); -- output: -00:01:01
SELECT TIMEDIFF("09:15", "09:05"); -- output: 00:10
SELECT TIMEDIFF("2022-09-16 09:12:13", "2022-09-13 09:11:12"); -- output: 72:01:01

In these outputs, it is possible to see that the format of the output can be adapted to the format specified in the input.
MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More