MySQL DATE_ADD()

Introduction to MySQL DATE_ADD()

DATE_ADD() function, as the name clearly states, it is a function that helps to alter the date and time in MySQL. This function updates and returns the date_time value as per the arguments which are explicitly provided within the brackets. Most of the intervals available in MySQL can be used in the DATE_ADD() function definition. The function lets us add not just positive values, but also negative values. The result can be a date_time value or a string as per the provided arguments. The date to be updated can be a dynamic function also.

Syntax

The DATE_ADD function should hold the date to be modified as well as the interval argument. So the syntax will be like:

DATE_ADD (date_time, INTERVAL value AddUnit);

Description:

• The ‘date_time’ is the value or date and time that is to be updated. This can be defined within single quotes (‘’) or can be a dynamic value as well (NOW()).
• ‘value’ is the value to be added. That means, it is the No. of minutes or days or hours or years that is to be added to the ‘date_time’
• ‘AddUnit’ is the type of unit to be added. This specifies whether to add hours or minutes or seconds or days or years. We can define a combination of units as well. Some examples of this are as follows:
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • MONTH
  • QUARTER
  • YEAR
  • YEAR_MONTH
  • DAY_HOUR
  • DAY_MINUTE
  • DAY_SECOND
  • HOUR_MINUTE
  • HOUR_SECOND
  • MINUTE_SECOND
  • SECOND_MICROSECOND

Note that the value returned will either be a date and time or a string based on the above three arguments.

We can look into some examples to understand the working of the DATE_ADD() function.

How does DATE_ADD() function work in MySQL?

The working function is very simple.

Syntax

DATE_ADD (date_time, INTERVAL value AddUnit);

From this syntax, the function is to add an interval of ‘value’  ‘AddUnits’ to ‘date_time’ and return the updated date_time. Just keep in mind, the date_time field is to follow the 24-hour clock. The key-word INTERVAL is mandatory in every statement.

Code #1

Let’s first see a dynamic value in the date_time field.

SELECT DATE_ADD(NOW(), INTERVAL 10 DAY) as date_time;

Here the function NOW() will return the current date and time in YYYY-MM-DD HH:MM: SS format. And 10 days are to be added to the current date_time as the output of this query.

Output:

Code #2

SELECT DATE_ADD('2020-01-10 10:00:00', INTERVAL 10 SECOND) as date_time;

So, the date to be updated here is 10 AM on 10^th January 2020. The interval to be added is 10 seconds.

Output:

The output has come as 10 AM 10 seconds, of 10th January 2020.

Code #3

SELECT DATE_ADD('2020-01-10 10:00:00', INTERVAL 10 HOUR) as date_time;

So, the date to be updated here is 10 AM of 10^th January 2020. The interval to be added is 10 hours.

Output:

The query says to add 10 hours to the date_time provided. So from 10AM, 10 hours is added which will make it 8PM same date.

We saw adding time frames to the date_time value. Let’s add number of days to the same and check.

Code #4

SELECT DATE_ADD('2020-01-10', INTERVAL 10 DAY) as date_time;

So, the date to be updated here is 10 AM of 10^th January 2020. The interval to be added is 10 days.

Output:

Adding 10 days to the date_time, will result in 10 AM of 20^th January, 2020. Cross check with calendar to confirm.

Code #5

SELECT DATE_ADD('2020-01-10 10:00:00', INTERVAL 10 WEEK) as date_time;

So, the date to be updated here is 10 AM of 10^th January 2020. The interval to be added is 10 weeks.

Output:

Here, the interval added is 10 weeks. So the date is to be shifted 10 weeks ahead of 10^th Jan, 2020. Thus the expected output will be 20^th March, 2020. Cross check with calendar to confirm.

Code #6

SELECT DATE_ADD('2020-01-10 10:00:00', INTERVAL 10 YEAR) as date_time;

So, the date to be updated here is 10 AM of 10^th January 2020. The interval to be added is 10 years.

Output:

When 10 years is added to the 10^th of January, 2020, the expected result is 10AM of 10^th January, 2030. We have added time and day units to the date_time field. Now let’s try adding a combination of these values.

Code #7

SELECT DATE_ADD('2020-01-10 10:00:00', INTERVAL '10:10' YEAR_MONTH) as date_time;

So, the date to be updated here is 10 AM of 10^th January 2020. The interval to be added is a combination of years and months. Here, 10 years and 10 months are to be added to the date_time value.

Output:

Adding 10 years and 10 months to 10^th January, 2020 will return 10^th November, 2030. The time will remain untouched in this query.

Code #8

SELECT DATE_ADD('2020-01-10 10:00:00', INTERVAL '1:10' HOUR_MINUTE) as date_time;

So, the date to be updated here is 10 AM of 10^th January 2020. The interval to be added is a combination of hours and minutes. Here, 1 hour and 10 minutes are to be added to the date_time value.

Output:

Adding 1 hour and 10 minutes to 10AM of 10^th January, 2020 will return 10 minutes past 11 AM of same date. The date will remain untouched in this query.

Let’s try adding negative values to this function.

Code #9

SELECT DATE_ADD('2020-01-10 10:00:00', INTERVAL -10 HOUR) as date_time;

Query is to subtract 10 hours from the date_time provided. In this date_time value, the date will remain unchanged and the expected output will be 12AM of the same date.

Output:

Code #10

SELECT DATE_ADD('2020-01-10 10:00:00', INTERVAL -10 DAY) as date_time;

So, 10 days are to be subtracted from 10^th January 2020. And the time field is to remain untouched.

Output:

Moving 10 days backwards in calendar from 10^th January 2020, will return 31^st December, 2019 and the time is to remain untouched.

Code #11

SELECT DATE_ADD('2020-01-10 10:00:00', INTERVAL '-60:-60' MINUTE_SECOND) as date_time;

The query directs to subtract 60 minutes and 60 seconds from 10 AM of 10^th January 2020.

Output:

By moving 60 minutes and 60 seconds backward to 10 AM, the expected result is 8:59 AM of the same day.

Advantage

The main advantage of this function is the ease of performing date arithmetic. Any value ahead or behind a specific date/time can easily be returned with this function.

Conclusion

We are now familiar with the DATE_ADD function. The function will add a specified unit of time or days to the value of the date and time provided in the statement. Both, positive and negative values can be added to the date_time field. As most of the time units in MySQL are supported by this function, the query is very easy and simple to amend any date and time field and return the result.

Recommended Articles

This is a guide to MySQL DATE_ADD(). Here we discuss an introduction to MySQL DATE_ADD(), syntax, how does it work with examples. You can also go through our other related articles to learn more –

1. MySQL Trigger
2. ROLLUP in MySQL
3. MySQL Subquery
4. Unique Key in MySQL