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.

Table of Contents

## Syntax of MySQL TIMEDIFF()

```
TIMEDIFF(time1, time2)
```

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;
```

And the output is –

### 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;
```

And the output is –

### 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;
```

And the output is –

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;
```

And the output is –

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;
```

And the output is –

### 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;
```

And the output is –

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;
```

And the output is –

### Working With Tables

Let us dive into some examples of `TIMEDIFF()`

with tables. Consider the below 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;
```

And the output is –

## 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

- MySQL Official Documentation on the
`TIMEDIFF()`

function.