MySQL - TIMESTAMPADD() Function

The TIMESTAMPADD() function of MySQL is used to add the specified time interval to a date time or, date expression.

The interval can be specified in any of the following units: MICROSECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR. They are specified with the same keywords.

This function returns NULL if any of its arguments is passed as NULL.

Syntax

Following is the syntax of TIMESTAMPADD() function −

TIMESTAMPADD(unit,interval,datetime_expr)

Parameters

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

  • unit is the interval type represented by the expr value which can be DAY, WEEK, MONTH, QUARTER, YEAR, HOUR, MINUTE, SECOND, MICROSECOND.

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

  • datetime_expr is the date or date-time expression to which you need to add the time interval.

Return value

This function returns a new date or time value after adding the specified interval to the original date or time.

Example

In the following query, we are using the MySQL TIMESTAMPADD() function to add 31 days to the specified date −

SELECT TIMESTAMPADD(DAY, 31, '2023-05-01') As Result;

Output

This will produce the following result −

Result
2023-06-01

Example

Here, we are adding 225 hours to the specified datetime value −

SELECT TIMESTAMPADD(HOUR, 225, '2023-05-23 20:40:32.88558')
As Result;

Output

Following is the output −

Result
2023-06-02 05:40:32.885580

Example

In the query below, we are subtracting 20 days from the specified date value −

SELECT TIMESTAMPADD(DAY,-20,'2023-09-05') As Result;

Output

Following is the output −

Result
2023-08-16

Example

In this example, we have created a table named ORDERS using the following CREATE TABLE query −

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

Now, insert the following records into the ORDERS table using the 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);

Execute the below query to fetch all the inserted records in the above-created table −

Select * From ORDERS;

Following is the ORDERS table −

OID DATE CUSTOMER_ID AMOUNT
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

Here, we are using the MySQL TIMESTAMPADD() function to add the 10 years to the datetime values in the "DATE" column −

SELECT OID, DATE, TIMESTAMPADD(YEAR, 10, DATE)
As Result FROM ORDERS;

Output

The output is displayed as follows −

OID DATE Result
102 2009-10-08 00:00:00 2019-10-08 00:00:00
100 2009-10-08 00:00:00 2019-10-08 00:00:00
101 2009-11-20 00:00:00 2019-11-20 12:00:00
103 2008-05-20 00:00:00 2018-05-20 00:00:00
Advertisements