TIMEDIFF() Function Examples – MySQL

TIMEDIFF() Function Examples – MySQL

Here, we will demonstrate how to works the timediff() function of MySQL and how to use this function with various examples.

We will take following examples

  • MySQL: how to get the difference between two timestamps in seconds?
  • 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

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

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.

If you have any questions or thoughts to share, use the comment form below to reach us.

Leave a Reply

Your email address will not be published. Required fields are marked *