MySQL TIMEDIFF() – Subtracting Time in MySQL

By /
TIMEDIFF Function

In this tutorial, we will study the MySQL TIMEDIFF() function. Suppose you are a manager at a company and you have data on the check-in date and time, and check-out date and time for each employee for a day. You have been tasked to find out their work hours. This would basically mean the difference of their check-in and check-out date and time values. One way to do this is manually – by subtracting the values yourself and entering them in your report. But we have a computer and we know MySQL, so let us make things easy for ourselves. Let us see how we can use the MySQL TIMEDIFF() function to solve this problem.

The MySQL TIMEDIFF() function is used to find the difference between two time or datetime values. Let us dive deep and take a look at the syntax before we move on to the examples.

Syntax of MySQL TIMEDIFF()

TIMEDIFF(time1, time2)Code language: SQL (Structured Query Language) (sql)

Where, ‘time1’ and ‘time2’ are two time or datetime expressions.

Note that TIMEDIFF() calculates the differences by subtracting time2 from time1 i.e. TIMEDIFF() computes and returns the value of ‘time1 – time2’.

Examples of MySQL TIMEDIFF()

Let us take a look at some basic examples of MySQL TIMEDIFF(). Let us use the TIMEDIFF() function to find the values of the following –

  • Time difference between 14:06:22 and 06:45:00 and,
  • Time difference between 20:25:42 and 10:00:00.

We will use the SELECT statement and an alias called ‘TimeDifference’ in our queries. The queries are –

SELECT TIMEDIFF("14:06:22", "06:45:00") AS TimeDifference; 
SELECT TIMEDIFF("20:25:42", "10:00:00") AS TimeDifference; Code language: SQL (Structured Query Language) (sql)

And the output is –

MySQL TIMEDIFF Basic Example

MySQL TIMEDIFF() With Large Values

The time value can represent elapsed time as well. So, we can use the TIMEDIFF() function with time values greater than 24 hours.  Let us use the MySQL TIMEDIFF() function to find the values of the following – 

  • Time difference between 622:45:22 and 15:10:33 and,
  • Time difference between 555:23:07 and 07:59:13.

The queries are –

SELECT TIMEDIFF("622:45:22", "15:10:33") AS TimeDifference; 
SELECT TIMEDIFF("555:23:07", "07:59:13") AS TimeDifference;Code language: SQL (Structured Query Language) (sql)

And the output is –

MySQL TIMEDIFF Elapsed Time

Limitation of the Time Data Type

Building on the previous example, let us find the time difference between the time values – 955:44:00 and 555:15:23. The query is –

SELECT TIMEDIFF("955:44:00", "555:15:23") AS TimeDifference;Code language: SQL (Structured Query Language) (sql)

And the output is –

Timediff Limitation 1

Wait a minute! We know basic maths that the difference of the hour values (955 – 555) should be a value greater than 283. Why did this happen? Besides, we have also got a warning. What went wrong?

One thing you should know while working with the time data type is that it has a major limitation. The time data type is limited to a range from -838:59:59 to 838:59:59. This means it does not recognize values out of that range. So, TIMEDIFF() subtracted 555:15:23 from 838:59:59 and gave us the result. Let us prove this by writing a query that subtracts 555:15:23 from 838:59:59.

SELECT TIMEDIFF("838:59:59", "555:15:23") AS TimeDifference;Code language: SQL (Structured Query Language) (sql)

And the output is –

Timediff Limitation 2

There you go! Remember this limitation of the time data type while working with the TIMEDIFF() function.

TIMEDIFF() With Negative Time Difference

If, by mistake, you pass the second value as a value larger than the first, you’ll get a negative value for the time difference. This is alright as TIMEDIFF() supports a negative time difference. Let us see an example of this. Consider the below query.

SELECT TIMEDIFF("02:44:51", "16:27:00") AS TimeDifference;Code language: SQL (Structured Query Language) (sql)

And the output is –

Timediff Negative

TIMEDIFF() With Datetime Values

Let us see an example of TIMEDIFF() with datetime values. Consider the below query.

SELECT TIMEDIFF("2020-12-15 12:00:00", "2020-11-15 12:00:00") AS TimeDifference;Code language: SQL (Structured Query Language) (sql)

And the output is –

Timediff Datetime 1

However, note that the example of limitation of time above is applicable with datetime values too. TIMEDIFF() cannot return the difference value outside of the range -838:59:59 to 838:59:59. Let us prove this using the below query where we take a 1 year difference.

SELECT TIMEDIFF("2020-12-15 12:00:00", "2021-11-15 12:00:00") AS TimeDifference;Code language: SQL (Structured Query Language) (sql)

And the output is –

Timediff Datetime 2

Working With Tables

Let us dive into some examples of TIMEDIFF() with tables. Consider the below Employee table.

Timediff Employee Table
Employee Table

Let us now take that problem I was talking about at the beginning of this article. You are a manager at a company and you have the above employee table. You have to find the work hours of every employee. Let us write a query for this using the TIMEDIFF() function.

SELECT eid, Name, TIMEDIFF(CheckOutDateTime, CheckInDateTime) AS 'Work Hours' FROM Employee;Code language: SQL (Structured Query Language) (sql)

And the output is –

Timediff Table Example

Conclusion

TIMEDIFF() is a very important datetime function. Basic subtraction operations are always very important and have abundant use cases. I would encourage you to practice more examples of this function!

References