MySQL - ADDTIME() Function

---

---

MySQL ADDTIME() Function

Unlike the ADDDATE() function, that only adds the specified time interval to a date value, the MySQL ADDTIME() function is used to add the specified time interval to a datetime or, time value.

This MySQL function accepts two expressions as arguments, adds the expression representing a time interval to the expression representing either date, datetime or time, and returns the final timestamp obtained. A NULL value is returned by this function if either of the two arguments is NULL.

Syntax

Following is the syntax of the above function −

ADDTIME(expr1,expr2);

Parameters

This method accepts two parameters. The same is described below −

• expr1 is the expression representing the datetime or time.

• expr2 is the expression representing the time interval to be added.

Return value

This function returns the sum of the initial time and the specified time interval.

Example

Following example demonstrates the usage of the ADDTIME() function −

SELECT ADDTIME('10:40:32.88558', '06:04:01.222222');

Output

Following output is obtained −

ADDTIME('10:40:32.88558', '06:04:01.222222')
16:44:34.107802

Example

In the following example we are passing DATETIME value for time −

SELECT ADDTIME('2018-05-23 05:40:32.88558', '06:04:01.222222');

Output

Following output is obtained −

ADDTIME('2018-05-23 05:40:32.88558', '06:04:01.222222')
2018-05-23 11:44:34.107802

Example

In the following example we are passing the result of the CURTIME() function as the time interval −

SELECT ADDTIME('2018-05-23 05:40:32.88558', CURTIME());

Output

Following output is obtained −

ADDTIME('2018-05-23 05:40:32.88558', CURTIME())
2018-05-23 17:58:41.885580

Example

We can also pass negative values as arguments to this function.

On a date and time value −

SELECT ADDTIME('2018-05-23 05:40:32.88558', '-06:04:01.222222');

Following output is obtained −

ADDTIME('2018-05-23 05:40:32.88558', '-06:04:01.222222')
2018-05-22 23:36:31.663358

On a timestamp −

SELECT ADDTIME('06:23:15.99999', '-02:25:11.11111');

Following output is obtained −

ADDTIME('06:23:15.99999', '-02:25:11.11111')
03:58:04.888880

Example

Let us create another table with name ORDERS in MySQL database using CREATE statement as follows −

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATE VARCHAR (20) NOT NULL,
   CUSTOMER_ID INT NOT NULL,
   AMOUNT DECIMAL (18, 2)
);

Now, we will insert the following records in ORDERS table using INSERT statement −

INSERT INTO ORDERS VALUES 
(102, '2009-10-08 00:00:00', 3, 3000.00),
(100, '2009-10-08 00:00:00', 3, 1500.00),
(101, '2009-11-20 00:00:00', 2, 1560.00),
(103, '2008-05-20 00:00:00', 4, 2060.00);

Following query adds time interval to the values in the column named DATE −

SELECT OID, DATE, ADDTIME(DATE, '12:45:50'), AMOUNT FROM ORDERS;

Output

The output is displayed as follows −

OID	DATE	ADDTIME(DATE, '12:45:50')	AMOUNT
102	2009-10-08 00:00:00	2009-10-22 12:45:50	3000.00
100	2009-10-08 00:00:00	2009-10-22 12:45:50	1500.00
101	2009-11-20 00:00:00	2009-12-04 12:45:50	1560.00
103	2008-05-20 00:00:00	2008-06-03 12:45:50	2060.00