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.
Recommended MySQL Tutorial
If you have any questions or thoughts to share, use the comment form below to reach us.
