Using date_add in MySQL to add intervals to dates

Like other database management systems, MySQL has a range of date functions which allow you to change the formatting of dates, get day/week/month/etc parts of dates, and add or subtract intervals to a specified date. This post looks at how to add intervals to a date in MySQL.

The output from the examples in this post were executed from the MySQL Command Line, using \G to execute the SQL command. I've then simply copied and pasted the query result into this page.

The DATE_ADD() function and its synonym ADDDATE() allow you to add or subtract an interval to the selected date, date function or date constant. DATE_SUB() and SUBDATE() work in the same way but the interval specified is subtracted. (If the interval was negatvie DATE_SUB() makes it positive).

The easiest way to explain is with an example. The first example selects the current date and one month from the current date:

mysql> SELECT NOW(), DATE_ADD(NOW(), INTERVAL 1 MONTH) \G
*************************** 1. row ***************************
                            NOW(): 2008-09-25 11:43:29
DATE_ADD(NOW(), INTERVAL 1 MONTH): 2008-10-25 11:43:29
1 row in set (0.00 sec)

The second example is the same, but for one month ago:

mysql> SELECT NOW(), DATE_ADD(NOW(), INTERVAL -1 MONTH) \G
*************************** 1. row ***************************
                             NOW(): 2008-09-25 11:43:57
DATE_ADD(NOW(), INTERVAL -1 MONTH): 2008-08-25 11:43:57
1 row in set (0.00 sec)

It is also possible to do this without calling the function at all and simply using arithmetic to add the interval to the datetime like so:

mysql> SELECT NOW(), NOW() + INTERVAL 1 MONTH \G
*************************** 1. row ***************************
                   NOW(): 2008-09-25 11:46:53
NOW() + INTERVAL 1 MONTH: 2008-10-25 11:46:53
1 row in set (0.01 sec)

My examples above all use NOW() as the datetime to add the interval to but it can just as easily be a column from a database query. For example if we have a table called "products" and it has a column called "backorder_date" which has a column type of date, we could run this query to add three days onto the back order date which is the value we might display on a website:

mysql> SELECT DATE_ADD(backorder_date, INTERVAL 3 DAY) AS backorder_date 
    -> FROM products LIMIT 1 \G
*************************** 1. row ***************************
backorder_date: 2008-10-18
1 row in set (0.00 sec)

For a complete list of the interval types check out the DATE_ADD() function on the MySQL website manual page.

It's very easy to add and subtract dates using MySQL. The are often circumstances when you would need to use this and do it in the database rather than in business logic or website code, such as calculating the backorder date of a product in the last example above.

Related posts:

• Convert a UNIX timestamp to a datetime with MySQL
• Setting the MySQL timezone per connection
• Formatting Dates and Times with MySQL