How to Use the DATE_SUB() Function in MySQL

Contents
In this article, you will learn how to use the DATE_SUB() function in MySQL.
Using the DATE_SUB() function in MySQL
The DATE_SUB() function in MySQL is used to subtract a specified interval from a date. It can be used to subtract seconds, minutes, hours, days, weeks, months, or years from a date. This function is useful when you need to calculate a past date or time based on a specific interval.
Syntax
DATE_SUB(date, INTERVAL value unit);
Examples
Here are some examples of using the DATE_SUB() function in MySQL:
Subtracting days from a date
To subtract a specified number of days from a date, you can use the DATE_SUB() function with the DAY interval.
Syntax
SELECT DATE_SUB(date, INTERVAL number_of_days DAY);
Example
SELECT DATE_SUB('2022-05-15', INTERVAL 7 DAY);
Output:
+-----------------------------------+
| DATE_SUB('2022-05-15', INTERVAL 7 DAY) |
+-----------------------------------+
| 2022-05-08 |
+-----------------------------------+
In this example, the DATE_SUB() function subtracts 7 days from the date ‘2022-05-15’ to return a past date of ‘2022-05-08’.
Subtracting months from a date
To subtract a specified number of months from a date, you can use the DATE_SUB() function with the MONTH interval.
Syntax
SELECT DATE_SUB(date, INTERVAL number_of_months MONTH);
Example
SELECT DATE_SUB('2022-05-15', INTERVAL 3 MONTH);
Output:
+------------------------------------+
| DATE_SUB('2022-05-15', INTERVAL 3 MONTH) |
+------------------------------------+
| 2022-02-15 |
+------------------------------------+
In this example, the DATE_SUB() function subtracts 3 months from the date ‘2022-05-15’ to return a past date of ‘2022-02-15’.
Subtracting years from a date
To subtract a specified number of years from a date, you can use the DATE_SUB() function with the YEAR interval.
Syntax
SELECT DATE_SUB(date, INTERVAL number_of_years YEAR);
Example
SELECT DATE_SUB('2022-05-15', INTERVAL 2 YEAR);
Output:
+----------------------------------+
| DATE_SUB('2022-05-15', INTERVAL 2 YEAR) |
+----------------------------------+
| 2020-05-15 |
+----------------------------------+
In this example, the DATE_SUB() function subtracts 2 years from the date ‘2022-05-15’ to return a past date of ‘2020-05-15’.
Subtracting time intervals from a dateTime value
You can also use the DATE_SUB() function to subtract time intervals from a datetime value.
Syntax
SELECT DATE_SUB(datetime_value, INTERVAL value unit);
Example
SELECT DATE_SUB('2022-05-15 08:30:00', INTERVAL 2 HOUR);
Output:
+--------------------------------------------------+
| DATE_SUB('2022-05-15 08:30:00', INTERVAL 2 HOUR) |
+--------------------------------------------------+
| 2022-05-15 06:30:00 |
+--------------------------------------------------+
In this example, the DATE_SUB() function subtracts 2 hours from the datetime value ‘2022-05-15 08:30:00’ to return a new datetime value of ‘2022-05-15 06:30:00’.