How to calculate the subtraction of dates using MySQL's DATE_SUB function

How to use MySQL's DATE_SUB function to calculate the subtraction of dates

In MySQL, the DATE_SUB function can be used to calculate the subtraction of dates. It allows us to subtract a specified time interval such as year, month, day, hour, minute, second, etc. from a given date. This allows us to perform date and time calculations easily.

The basic syntax of the DATE_SUB function is as follows:

DATE_SUB(date, INTERVAL value unit)

Among them, date is the date to be calculated, and value is the time to be subtracted. Quantity, unit is the unit of time. The following are some commonly used time units:

• YEAR: Year
• MONTH: Month
• DAY: Day
• HOUR: Hour
• MINUTE: Minutes
• SECOND: Seconds

Next, let us demonstrate how to use the DATE_SUB function through some examples.

Example 1: Calculate the date one day before the specified date

SELECT DATE_SUB('2022-01-01', INTERVAL 1 DAY);

Output result:

2021-12-31

In this example, we use the DATE_SUB function to convert the specified date "2022-01- 01』subtracted 1 day to get the previous day's date "2021-12-31".

Example 2: Calculate the date one month before the specified date

SELECT DATE_SUB('2022-01-01', INTERVAL 1 MONTH);

Output result:

2021-12-01

In this example, we use the DATE_SUB function to convert the specified date to 『2022-01 -01" subtracted 1 month to get the date of the previous month "2021-12-01".

Example 3: Calculate the date one year before the specified date

SELECT DATE_SUB('2022-01-01', INTERVAL 1 YEAR);

Output result:

2021-01-01

In this example, we use the DATE_SUB function to convert the specified date to 『2022-01 -01" subtracted 1 year to get the date of the previous year "2021-01-01".

In addition to the above examples, we can also use the DATE_SUB function with other MySQL functions to achieve more complex date calculations. For example, we can use the DATE_SUB function to calculate the number of days between two dates.

Example 4: Calculate the number of days difference between two dates

SELECT DATEDIFF('2022-01-01', DATE_SUB('2021-12-01', INTERVAL 1 YEAR));

Output result:

30

In this example, we first use the DATE_SUB function to convert the date "2021- "12-01" minus 1 year, we get "2020-12-01". Then, we use the DATEDIFF function to calculate the number of days between the specified date "2022-01-01" and the calculated date "2020-12-01", and the result is 30 days.

Through the above example, we can see that using MySQL's DATE_SUB function can conveniently perform date subtraction calculations. Whether it is simple date subtraction or complex date interval calculations, the DATE_SUB function provides flexible and powerful functions. I hope this article can help readers better understand how to use the DATE_SUB function for date subtraction calculations.

The above is the detailed content of How to calculate the subtraction of dates using MySQL's DATE_SUB function. For more information, please follow other related articles on the PHP Chinese website!