JIYIK CN >

This article shows you how to add dates and times in MySQL using DATE_ADD()the _date_time_s() function. We will learn how to add or subtract days, years, months, and times individually, as well as how to combine the two (for example, days and hours).

DATE_ADD() Method in MySQL

We can DATE_ADD()add a time interval to a value of type DATETIME or DATE using the method. It accepts two parameters.

The first parameter is start_date, which is a start date. It can be of DATE/DATETIME type.

The second argument is the INTERVAL expression unit, which is the interval value to which the start_date value should be added. You can find a list of units that we can use this method with.

If the first argument is a DATETIME or interval value containing a time element (hours, minutes, or seconds), it returns a DATETIME value; otherwise, it returns a string.

Create a table in MySQL and insert data

To learn DATE_ADD()the method, let's create a table called trips with columns id, country_name, and startDate where startDate is of type DATETIME. You can also create a trips table and fill it with some random data given below to follow along.

CREATE TABLE trips(
  id INT NOT NULL AUTO_INCREMENT,
  country_name VARCHAR(45) NOT NULL,
  startDate DATETIME NOT NULL,
  PRIMARY KEY (id));

INSERT INTO trips (country_name, startDate) VALUES
('Pakistan', '2019-01-27 01:23:34'),
('USA', '2019-02-22 12:34:05'),
('Turkey', '2020-05-14 08:03:02'),
('India', '2020-01-21 11:20:04');

SELECT * FROM trips;

Output:

| id   | country_name | startDate           |
| ---- | ------------ | ------------------- |
| 1    | Pakistan     | 2019-01-27 01:23:34 |
| 2    | USA          | 2019-02-22 12:34:05 |
| 3    | Turkey       | 2020-05-14 08:03:02 |
| 4    | India        | 2020-01-21 11:20:04 |

Adding Days in MySQL Using DATE_ADD() Function

Add 2 days to the startDate of the trips table with id 3.

Sample code:

UPDATE trips
SET startDate = DATE_ADD(startDate , INTERVAL 2 DAY)
WHERE id = 3;

Output:

| id   | country_name | startDate           |
| ---- | ------------ | ------------------- |
| 1    | Pakistan     | 2019-01-27 01:23:34 |
| 2    | USA          | 2019-02-22 12:34:05 |
| 3    | Turkey       | 2020-05-16 08:03:02 |
| 4    | India        | 2020-01-21 11:20:04 |

We can also subtract days as follows.

Sample code:

UPDATE trips
SET startDate = DATE_ADD(startDate , INTERVAL -2 DAY)
WHERE id = 1;

Output:

| id   | country_name | startDate           |
| ---- | ------------ | ------------------- |
| 1    | Pakistan     | 2019-01-25 01:23:34 |
| 2    | USA          | 2019-02-22 12:34:05 |
| 3    | Turkey       | 2020-05-16 08:03:02 |
| 4    | India        | 2020-01-21 11:20:04 |

Add Month and Year in MySQL using DATE_ADD() Function

Like days, we can also add years and months. To subtract a number of years or months, write an expression with a minus sign (-ve).

Sample code:

#add month
UPDATE trips
SET startDate = DATE_ADD(startDate , INTERVAL 1 MONTH)
WHERE id = 1;

#add year
UPDATE trips
SET startDate = DATE_ADD(startDate , INTERVAL 1 YEAR)
WHERE id = 1;

Output:

| id   | country_name | startDate           |
| ---- | ------------ | ------------------- |
| 1    | Pakistan     | 2020-02-25 01:23:34 |
| 2    | USA          | 2019-02-22 12:34:05 |
| 3    | Turkey       | 2020-05-16 08:03:02 |
| 4    | India        | 2020-01-21 11:20:04 |

Add hours, minutes and seconds in MySQL using DATE_ADD() function

Add 1 hour, minute, and second to startDate in the trips table with id 4.

Sample code:

#add hours
UPDATE trips
SET startDate = DATE_ADD(startDate , INTERVAL 1 HOUR)
WHERE id = 4;

#add minutes
UPDATE trips
SET startDate = DATE_ADD(startDate , INTERVAL 1 MINUTE)
WHERE id = 4;

#add seconds
UPDATE trips
SET startDate = DATE_ADD(startDate , INTERVAL 1 SECOND)
WHERE id = 4;

Output:

| id   | country_name | startDate           |
| ---- | ------------ | ------------------- |
| 1    | Pakistan     | 2020-02-25 01:23:34 |
| 2    | USA          | 2019-02-22 12:34:05 |
| 3    | Turkey       | 2020-05-16 08:03:02 |
| 4    | India        | 2020-01-21 12:21:05 |

Add or subtract dates and times in MySQL using the DATE_ADD() function

Add 2 hours and 2 minutes to the startDate of the trips table with id 3.

#add hours and minutes
UPDATE trips
SET startDate = DATE_ADD(startDate , INTERVAL '2:2' HOUR_MINUTE)
WHERE id = 3;

Output:

| id   | country_name | startDate           |
| ---- | ------------ | ------------------- |
| 1    | Pakistan     | 2020-02-25 01:23:34 |
| 2    | USA          | 2019-02-22 12:34:05 |
| 3    | Turkey       | 2020-05-16 10:05:02 |
| 4    | India        | 2020-01-21 12:21:05 |

Likewise, we can use DAY_HOUR, YEAR_MONTH, etc.