SQL Tutorial SQL Advanced SQL Database SQL References

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:

EmpIDNameLogin StampExpected Logout Stamp
1John2019-10-25 09:20:382019-10-25 17:50:38
2Marry2019-10-25 09:21:052019-10-25 17:51:05
3Jo2019-10-25 09:24:352019-10-25 17:54:35
4Kim2019-10-25 09:25:242019-10-25 17:55:24
5Ramesh2019-10-25 09:27:162019-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:

EmpIDNameLogin StampExpected Logout Stamp
1John2019-10-25 09:20:382019-10-25 17:50:38
2Marry2019-10-25 09:21:052019-10-25 17:51:05
3Jo2019-10-25 09:24:352019-10-25 17:54:35
4Kim2019-10-25 09:25:242019-10-25 17:55:24
5Ramesh2019-10-25 09:27:162019-10-25 17:57:16
6Suresh2019-10-25 09:28:192019-10-25 17:58:19

❮ MySQL Functions