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 the following examples on

  • 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.

You may like

  1. TIMESTAMP() Function Examples – MySQL
  2. SEC_TO_TIME() Function Examples – MySQL
  3. QUARTER() Function Examples – MySQL
  4. STR_TO_DATE() Function Examples – MySQL
  5. PERIOD_DIFF() Function Examples – MySQL
  6. PERIOD_ADD() Function Examples – MySQL
  7. SECOND() Function Examples – MySQL
  8. MICROSECOND() Function Examples – MySQL
  9. MAKETIME() Function Examples – MySQL
  10. Localtime() Function Examples – MySQL
  11. CURTIME() Function Examples – MySQL
  12. MySQL CURRENT TIMESTAMP Function Examples
  13. MySQL CURRENT TIME Function Examples
  14. ADDTIME() Function Examples – MySQL
  15. MINUTE() Function Examples – MySQL
  16. HOUR() Function Examples – MySQL
  17. NOW() MySQL Function | MySQL
  18. MySQL DATE() And TIME() Functions (List)
  19. TIMESTAMPADD() Function Examples – MySQL
  20. TIMESTAMPDIFF() Function Examples – MySQL

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 *