MySQL Tutorial MySQL Advanced MySQL Database Account Management MySQL References

MySQL ADDTIME() Function



The MySQL ADDTIME() function adds a specified time interval to 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 SUBTIME() function.

Syntax

ADDTIME(datetime, time)

Parameters

datetime Required. Specify the time/datetime value to which the time interval should be added.
time Required. Specify the time interval to add to 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 ADDTIME() function.

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

mysql> SELECT ADDTIME('2019-10-25 08:10:25', '10:5.000010');
Result: '2019-10-25 18:15:25.000010'

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

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

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

mysql> SELECT ADDTIME('01:20:35', '5.000010');
Result: '01:20:40.000010'

mysql> SELECT ADDTIME('01:20:35', '10:5.000010');
Result: '11:25:35.000010'

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

mysql> SELECT ADDTIME('01:20:35', '-5:10:5.000010');
Result: '-03:49:30.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 query can be used.

INSERT INTO EmployeeLogin 
VALUES (6, 'Suresh', NOW(), ADDTIME(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

5