MySQL TIMEDIFF() Function Examples

MySQL TIMEDIFF() fucntion; In this tutorial, we will learn how to get the difference between two datetime in the mysql using timediff() function with the help of examples.

And as well as take two example using timediff(); as shown below:

how to get the difference between two timestamps in seconds MySQL?
MySQL: convert timediff() to seconds using the MySQL database table.

MySQL TIMEDIFF() Function

In MySQL, the TIMEDIFF () function is used to return the difference between two time or DateTime components.

Note: TIMEDIFF () requires two arguments for comparison, and TIMEDIFF () subtracts the second argument from the first, then returns the result as the time value.

Syntax

The syntax goes like this:

TIMEDIFF(agr1,agr2)

Here, agr1 and agr2 are the two values to compare. The return value is agr2 subtracted from agr1 .

Example-1

Let’s take the first simple and basic example of this function:

SELECT TIMEDIFF('09:45:35', '07:15:15');

Output-1

+----------------------------------+
| TIMEDIFF('09:45:35', '07:15:15') |
+----------------------------------+
|  02:30:20                        |
+----------------------------------+

Example-2

The time value can represent the time elapsed, so it is not limited to less than 24 hours.

SELECT TIMEDIFF('350:40:10', '11:35:25');

Output-2

+-----------------------------------+
| TIMEDIFF('350:40:10', '11:35:25') |
+-----------------------------------+
|  339:04:45                        |
+-----------------------------------+

Example-3

Let’s take the next example, suppose that the second value is larger than first value, you will get a negative value for the difference in time. It is completely legitimate.

SELECT TIMEDIFF('10:35:25', '350:40:10');

Output-3

+-----------------------------------+
| TIMEDIFF('10:35:25', '350:40:10') |
+-----------------------------------+
|  -339:04:45                       |
+-----------------------------------+

Example-4

Now we take another example of MySQL timediff() function with DateTime values

SELECT TIMEDIFF('2020-04-01 11:45:25', '2020-02-01 11:25:25');

Output-4

+--------------------------------------------------------+
| TIMEDIFF('2020-04-01 11:45:25', '2020-02-01 11:25:25') |
+--------------------------------------------------------+
| 838:59:59                                              |
+--------------------------------------------------------+

Note: that both the arguments must be of the same type.

Example-5

how to get the difference between two timestamps in seconds MySQL?

Use the below MySQL query for getting the difference :

SELECT TIME_TO_SEC(TIMEDIFF('2019-08-20 12:01:00', '2019-08-20 12:00:00')) diff;

Output-5

+------+
| diff |
+------+
|   60 |
+------+
1 row in set (0.00 sec)

Example-6

MySQL: convert timediff() to seconds using the MySQL database table.

Use the below query for mysql database table.

SELECT TIME_TO_SEC(timediff(time_end, time_begin)) from table_name

Conclusion

Here, you have learned how to use MySQL timediff() function with various examples.