How to Use the Date_ADD() Function to Add Days and Time in MySQL
-
the
DATE_ADD()Method in MySQL - Create a Table and Insert Data in MySQL
-
Use the
DATE_ADD()Function to Add Days in MySQL -
Use the
DATE_ADD()Function to Add Month and Year in MySQL -
Use the
DATE_ADD()Function to Add Hours, Minutes, and Seconds in MySQL -
Use the
DATE_ADD()Function to Add or Minus the Day and Time in MySQL
This tutorial shows how to use the DATE_ADD() function to add days and time in MySQL. We’ll learn how to add or reduce days, years, months, and time individually and combine the two (for instance, day & hour).
the DATE_ADD() Method in MySQL
We can use the DATE_ADD() method to add an interval to a value of the DATETIME or DATE type. It accepts two arguments.
The first argument is start_date, which is a beginning date. It can be of the DATE/DATETIME type.
The second argument is the INTERVAL expression unit, which is the interval value that needs to be added to the specified value of start_date. You can find the list of units that we can use with this method.
It returns the DATETIME value if the first argument is DATETIME or the interval value contains the time element (hour, minutes, or seconds); otherwise, it returns a string.
Create a Table and Insert Data in MySQL
To learn the DATE_ADD() method, let’s create a table named trips that has the id, country_name and startDate columns where startDate is of DATETIME type. You can also create a trips table and populate 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 |
Use the DATE_ADD() Function to Add Days in MySQL
Add 2 days in the startDate of the trips table where id is 3.
Example 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.
Example 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 |
Use the DATE_ADD() Function to Add Month and Year in MySQL
Like days, we can also add the year and month. To minus some years or months, write an expression with a minus (-ve) sign.
Example 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 |
Use the DATE_ADD() Function to Add Hours, Minutes, and Seconds in MySQL
Add 1 hour, minute, and second in the startDate of the trips table where id is 4.
Example 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 |
Use the DATE_ADD() Function to Add or Minus the Day and Time in MySQL
Add 2 hours and 2 minutes in the startDate of the trips table where id is 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 |
Similarly, we can use the DAY_HOUR, YEAR_MONTH and more.
