Use MySQL's DATE_ADD function to add and subtract dates

Use MySQL's DATE_ADD function to perform date addition and subtraction operations

Date addition and subtraction operations are often encountered in development, such as calculating dates in the next few days or calculating dates in the past few months. MySQL provides the DATE_ADD function, which can easily add and subtract dates. This article will introduce the basic usage of the DATE_ADD function in detail and give relevant code examples.

1. The basic syntax of the DATE_ADD function
   The syntax of the DATE_ADD function is as follows:
   DATE_ADD(date, INTERVAL expr interval_type)

Among them, date is to be added The date of the subtraction operation, expr is the value to be added or subtracted, and interval_type specifies the time unit to be added or subtracted.

2. Date addition operation
   To add dates, you can use the DATE_ADD function and specify interval_type as DAY, WEEK, MONTH, YEAR and other time units. Here is an example that calculates the date three days from today:

SELECT DATE_ADD(CURDATE(), INTERVAL 3 DAY) AS future_date;

The output is as follows:

+-------------+
| future_date |
+-------------+
| 2021-02-04  |
+-------------+

In the above example, CURDATE() gets the current date, and then Use the DATE_ADD function to add it, add 3 days, and get the date three days in the future.

3. Date subtraction operation
   To subtract a date, you can use the DATE_ADD function and specify a negative expr value. Here is an example to calculate the date five days ago starting from today:

SELECT DATE_ADD(CURDATE(), INTERVAL -5 DAY) AS past_date;

The output is as follows:

+------------+
| past_date  |
+------------+
| 2021-01-27 |
+------------+

In the above example, CURDATE() gets the current date, Then use the DATE_ADD function to subtract it, subtracting 5 days, and get the date five days ago.

4. Other date addition and subtraction operations
   In addition to DAY, the DATE_ADD function can also perform addition and subtraction operations on dates in other time units. Here are some commonly used examples:

• Add and subtract months:

  SELECT DATE_ADD(CURDATE(), INTERVAL 2 MONTH) AS new_date;

  This example means adding 2 months to the current date.

• Add and subtract years:

  SELECT DATE_ADD(CURDATE(), INTERVAL 1 YEAR) AS new_date;

  This example means adding 1 year to the current date.

• Add and subtract weeks:

  SELECT DATE_ADD(CURDATE(), INTERVAL 3 WEEK) AS new_date;

  This example means adding 3 weeks to the current date.

  5. Summary
     The DATE_ADD function is a very useful function provided by MySQL, which can easily implement the addition and subtraction of dates. We can easily calculate future or past dates by specifying the date, adding a time value, and the time unit. During development, the DATE_ADD function must be used flexibly to handle date-related operations more efficiently.

  The above is the detailed content of Use MySQL's DATE_ADD function to add and subtract dates. For more information, please follow other related articles on the PHP Chinese website!