SQL Tutorial SQL Advanced SQL Database SQL References

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:

EmpIDNameDateLoginTimeLogoutTime
1John2019-10-2509:20:3817:55:38
2Marry2019-10-2509:21:0517:50:38
3Jo2019-10-2509:24:3518:20:38
4Kim2019-10-2509:25:2417:50:38
5Ramesh2019-10-2509:27:1618: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:

EmpIDNameDateLoginTimeLogoutTimeOfficeTime
1John2019-10-2509:20:3817:55:3808:35:00
2Marry2019-10-2509:21:0517:50:3808:29:33
3Jo2019-10-2509:24:3518:20:3808:56:03
4Kim2019-10-2509:25:2417:50:3808:25:14
5Ramesh2019-10-2509:27:1618:10:3808:43:22

❮ MySQL Functions