How to add and subtract on dates using DATE_ADD function in MySQL

How to use the DATE_ADD function in MySQL to perform addition and subtraction operations on dates

MySQL is a commonly used relational database management system that provides a wealth of functions and operators to process date and time data . Among them, the DATE_ADD function can perform addition and subtraction operations on dates, which is very convenient and practical. This article will introduce how to use the DATE_ADD function in MySQL to perform date operations and provide some code examples for reference.

1. The syntax and parameters of the DATE_ADD function

The syntax of the DATE_ADD function is as follows:
DATE_ADD(date, INTERVAL expr unit)

Among them, date Is a parameter representing a date, which can be a date value, date column or an expression that returns a date value;
expr is the value that needs to be added or subtracted;
unit is the unit that needs to be added or subtracted, It can be YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, etc.

2. Usage examples of DATE_ADD function

Let’s look at some usage examples of DATE_ADD function.

Example 1: Add one day to the specified date

SELECT DATE_ADD('2021-01-01', INTERVAL 1 DAY);

Executing the above code will return the result "2021-01-02", which means adding one day to the date '2021-01-01'.

Example 2: Add one month to the specified date

SELECT DATE_ADD('2021-01-01', INTERVAL 1 MONTH);

Executing the above code will return the result "2021-02-01", which means adding one to the date '2021-01-01' moon.

Example 3: Add one hour to the specified date

SELECT DATE_ADD('2021-01-01 12:00:00', INTERVAL 1 HOUR);

Executing the above code will return the result "2021-01-01 13:00:00", which is the date and time '2021-01 -01 12:00:00' plus one hour.

Example 4: Add a year to the specified date

SELECT DATE_ADD('2021-01-01', INTERVAL 1 YEAR);

Executing the above code will return the result "2022-01-01", which means adding one to the date '2021-01-01' Year.

Example 5: Subtract one month from the specified date

SELECT DATE_ADD('2021-01-01', INTERVAL -1 MONTH);

Executing the above code will return the result "2020-12-01", which means subtracting one from the date '2021-01-01' moon.

3. Other uses of the DATE_ADD function

In addition to using the DATE_ADD function directly in the query statement, we can also use it in the INSERT or UPDATE statement to implement date manipulation Modification of fields.

Example 6: Insert the specified date plus one month into the database

INSERT INTO table_name (date_column)
VALUES (DATE_ADD('2021-01-01', INTERVAL 1 MONTH));

Executing the above code will add the date '2021-01-01' to the result of one month, "2021- 02-01" is inserted into the corresponding field of the database.

Example 7: Update the date field in the database to the specified date minus one week

UPDATE table_name
SET date_column = DATE_ADD(date_column, INTERVAL -1 WEEK)
WHERE condition;

Executing the above code will subtract one week from the date field of the records that meet the conditions in the database.

Summary:

This article introduces how to use the DATE_ADD function in MySQL to perform addition and subtraction operations on dates. We can easily operate on dates by specifying the unit and the value to be added or subtracted. In practical applications, the DATE_ADD function can be flexibly used to process date fields according to specific needs. I hope this article will be helpful to everyone in learning and using MySQL's DATE_ADD function.

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