MySQL SUBTIME() Function

The MySQL SUBTIME() function subtracts a specified time interval from a time/datetime value and then returns the modified time/datetime value.

Using this function with a negative time value as a parameter is equivalent to using the ADDTIME() function.

Syntax

SUBTIME(datetime, time)

Parameters

`datetime`	`Required.` Specify the time/datetime value from which the time interval should be subtracted.
`time`	`Required.` Specify the time interval to subtract from datetime. Both positive and negative values are allowed.

Return Value

Returns the modified time/datetime value.

Example 1:

The example below shows the usage of SUBTIME() function.

mysql> SELECT SUBTIME('2019-10-25 08:10:25', '5.000010');
Result: '2019-10-25 08:10:19.999990'

mysql> SELECT SUBTIME('2019-10-25 08:10:25', '10:5.000010');
Result: '2019-10-24 22:05:24.999990'

mysql> SELECT SUBTIME('2019-10-25 08:10:25', '2:10:5.000010');
Result: '2019-10-25 06:00:19.999990'

mysql> SELECT SUBTIME('2019-10-25 08:10:25', '3 2:10:5.000010');
Result: '2019-10-22 06:00:19.999990'

mysql> SELECT SUBTIME('2019-10-25 08:10:25', '-3 2:10:5.000010');
Result: '2019-10-28 10:20:30.000010'

mysql> SELECT SUBTIME('01:20:35', '5.000010');
Result: '01:20:29.999990'

mysql> SELECT SUBTIME('01:20:35', '10:5.000010');
Result: '-08:44:25.000010'

mysql> SELECT SUBTIME('01:20:35', '5:10:5.000010');
Result: '-03:49:30.000010'

mysql> SELECT SUBTIME('01:20:35', '-5:10:5.000010');
Result: '06:30:40.000010'

Example 2:

Consider a database table called EmployeeLogin with the following records:

EmpID	Name	Login Stamp	Expected Logout Stamp
1	John	2019-10-25 09:20:38	2019-10-25 17:50:38
2	Marry	2019-10-25 09:21:05	2019-10-25 17:51:05
3	Jo	2019-10-25 09:24:35	2019-10-25 17:54:35
4	Kim	2019-10-25 09:25:24	2019-10-25 17:55:24
5	Ramesh	2019-10-25 09:27:16	2019-10-25 17:57:16

To insert a new record in this table, the following statement can be used.

INSERT INTO EmployeeLogin 
VALUES (6, 'Suresh', NOW(), SUBTIME(NOW(), '-8:30');

-- see the result
SELECT * FROM EmployeeLogin;

This will produce a result similar to:

EmpID	Name	Login Stamp	Expected Logout Stamp
1	John	2019-10-25 09:20:38	2019-10-25 17:50:38
2	Marry	2019-10-25 09:21:05	2019-10-25 17:51:05
3	Jo	2019-10-25 09:24:35	2019-10-25 17:54:35
4	Kim	2019-10-25 09:25:24	2019-10-25 17:55:24
5	Ramesh	2019-10-25 09:27:16	2019-10-25 17:57:16
6	Suresh	2019-10-25 09:28:19	2019-10-25 17:58:19

❮ MySQL Functions