In MySQL, you can use the DATE_SUB() function to subtract a specified amount of time from a date. For example, you can use it to subtract 7 days from a given date. You can specify whether to subtract days, weeks, months, quarters, years, etc. You can also subtract a time value, such as seconds, microseconds, etc.

This function is similar to DATE_ADD(), except that it subtracts from a date instead of adds to it.

Syntax

The syntax goes like this:

DATE_SUB(date,INTERVAL expr unit)

Example 1 – Basic Usage

Here’s an example of usage.

SELECT DATE_SUB('2021-05-07', INTERVAL 5 DAY) AS Result;

Result:

+------------+
| Result     |
+------------+
| 2021-05-02 |
+------------+

This example subtracts 5 days from the date supplied by the first argument.

Example 2 – Other Date Units

You can specify the units in days, weeks, months, years, etc. Here are some examples.

SELECT 
    '2021-05-07' AS 'Start Date',
    DATE_SUB('2021-05-07', INTERVAL 2 WEEK) AS '-2 Weeks',
    DATE_SUB('2021-05-07', INTERVAL 2 MONTH) AS '-2 Months',
    DATE_SUB('2021-05-07', INTERVAL 2 QUARTER) AS '-2 Quarters',
    DATE_SUB('2021-05-07', INTERVAL 2 YEAR) AS '-2 Years';

Result:

+------------+------------+------------+-------------+------------+
| Start Date | -2 Weeks   | -2 Months  | -2 Quarters | -2 Years   |
+------------+------------+------------+-------------+------------+
| 2021-05-07 | 2021-04-23 | 2021-03-07 | 2020-11-07  | 2019-05-07 |
+------------+------------+------------+-------------+------------+

Example 3 – Time Units

You can also subtract time units from a date/time value. Here’s an example.

SELECT DATE_SUB('2021-05-07 10:00:00', INTERVAL 5 HOUR) AS Result;

Result:

+---------------------+
| Result              |
+---------------------+
| 2021-05-07 05:00:00 |
+---------------------+

And you can specify multiple units at the same time. For example, you can specify hours and minutes. Like this.

SELECT DATE_SUB('2021-05-07 10:00:00', INTERVAL '1:30' HOUR_MINUTE) AS Result;

Result:

+---------------------+
| Result              |
+---------------------+
| 2021-05-07 08:30:00 |
+---------------------+

Expected Values

The following table shows the valid unit values and their expected format.

unit Value	Expected expr Format
MICROSECOND	MICROSECONDS
SECOND	SECONDS
MINUTE	MINUTES
HOUR	HOURS
DAY	DAYS
WEEK	WEEKS
MONTH	MONTHS
QUARTER	QUARTERS
YEAR	YEARS
SECOND_MICROSECOND	‘SECONDS.MICROSECONDS’
MINUTE_MICROSECOND	‘MINUTES:SECONDS.MICROSECONDS’
MINUTE_SECOND	‘MINUTES:SECONDS’
HOUR_MICROSECOND	‘HOURS:MINUTES:SECONDS.MICROSECONDS’
HOUR_SECOND	‘HOURS:MINUTES:SECONDS’
HOUR_MINUTE	‘HOURS:MINUTES’
DAY_MICROSECOND	‘DAYS HOURS:MINUTES:SECONDS.MICROSECONDS’
DAY_SECOND	‘DAYS HOURS:MINUTES:SECONDS’
DAY_MINUTE	‘DAYS HOURS:MINUTES’
DAY_HOUR	‘DAYS HOURS’
YEAR_MONTH	‘YEARS-MONTHS’

You can also use the SUBDATE() function to do the same thing (it’s a synonym for the DATE_SUB() function when using the same syntax).

In addition, the syntax of SUBDATE() has a second form, which is a shorthand method of subtracting a certain number of days from a date. For more info, see SUBDATE() Examples in MySQL.