How to use the DATE_SUB function to perform subtraction on dates in MySQL

How to use the DATE_SUB function in MySQL to perform subtraction operations on dates

Introduction:
In the MySQL database, the DATE_SUB function is a function used to perform subtraction operations on dates. This function can easily implement subtraction operations on dates, such as calculating the date of a certain day in the past, calculating the date of a period of time ago, etc.

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

Parameter description:

• date: required The date on which the subtraction operation was performed. It can be a specific date or a date and time field;
• expr: the value to be subtracted. It can be an integer or a numerical field;
• unit: the unit to be subtracted. It can be YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND and other units;

Code examples:
The following are several specific examples of using the DATE_SUB function for subtraction operations.

1. Calculate the date of a certain day in the past:
   Assume that the current date is 2021-09-15, and we want to calculate the date 5 days ago. You can use the following code:

   SELECT DATE_SUB('2021-09-15', INTERVAL 5 DAY);

   Copy after login

   The running result is: 2021-09-10, which is the date 5 days before the current date.

2. Calculate the date some time ago:
   Suppose we want to calculate the date and time 30 minutes ago. You can use the following code:

   SELECT DATE_SUB(NOW(), INTERVAL 30 MINUTE);

   Copy after login

   The running result is the date and time 30 minutes before the current date and time.

3. Calculate the date some time ago:
   Suppose we have an order table, in which there is a field that is the order creation time created_at. We want to query orders created a month ago. You can use the following code:

   SELECT * FROM orders WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 MONTH);

   Copy after login

   This query will return all orders created one month ago.

Summary:
In the MySQL database, the DATE_SUB function is a very useful function that can perform subtraction operations on dates. By flexibly using the DATE_SUB function, various operations and calculations on dates can be realized, improving the flexibility and accuracy of data queries.

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