MySQL TIMEDIFF() Function
The MySQL TIMEDIFF() function returns the difference between two time/datetime values. The returned value is expressed as a time value.
When using the TIMEDIFF() function, both arguments must be either time values or datetime values. When one argument is used as time value and another argument is used as datetime value, this function returns NULL value.
A time value (returned value) can range from '-838:59:59' to '838:59:59'.
Syntax
TIMEDIFF(time1, time2)
Parameters
time1 |
Required. Specify the first time or datetime value to calculate the difference (time1 - time2). |
time2 |
Required. Specify the second time or datetime value to calculate the difference (time1 - time2). |
Return Value
Returns the difference between two time/datetime values.
Example 1:
The example below shows the usage of TIMEDIFF() function.
mysql> SELECT TIMEDIFF('12:40:45', '10:20:25'); Result: '02:20:20' mysql> SELECT TIMEDIFF('2019-10-25 12:40:45', '2019-10-25 10:20:25'); Result: '02:20:20' mysql> SELECT TIMEDIFF('2019-10-25 12:40:45.001234', '2019-10-25 10:20:25.001000'); Result: '02:20:20.000234' mysql> SELECT TIMEDIFF('10:20:25', '12:40:45'); Result: '-02:20:20' mysql> SELECT TIMEDIFF('2019-10-25 12:40:45', '10:20:25'); Result: NULL
Example 2:
Consider a database table called EmployeeLogin with the following records:
EmpID | Name | Date | LoginTime | LogoutTime |
---|---|---|---|---|
1 | John | 2019-10-25 | 09:20:38 | 17:55:38 |
2 | Marry | 2019-10-25 | 09:21:05 | 17:50:38 |
3 | Jo | 2019-10-25 | 09:24:35 | 18:20:38 |
4 | Kim | 2019-10-25 | 09:25:24 | 17:50:38 |
5 | Ramesh | 2019-10-25 | 09:27:16 | 18:10:38 |
To calculate the time spent by each employee in the office, the following query can be used:
SELECT *, TIMEDIFF(LogoutTime, LoginTime) AS OfficeTime FROM EmployeeLogin;
This will produce a result similar to:
EmpID | Name | Date | LoginTime | LogoutTime | OfficeTime |
---|---|---|---|---|---|
1 | John | 2019-10-25 | 09:20:38 | 17:55:38 | 08:35:00 |
2 | Marry | 2019-10-25 | 09:21:05 | 17:50:38 | 08:29:33 |
3 | Jo | 2019-10-25 | 09:24:35 | 18:20:38 | 08:56:03 |
4 | Kim | 2019-10-25 | 09:25:24 | 17:50:38 | 08:25:14 |
5 | Ramesh | 2019-10-25 | 09:27:16 | 18:10:38 | 08:43:22 |
❮ MySQL Functions