MySQL DATE_SUB() Function with Examples

How to Use the MySQL DATE_SUB() Function

In this tutorial, you’ll learn how to use the MySQL DATE_SUB() function. The DATE_SUB() function subtracts a time and date interval from a given date and returns a new date.

To add the date and time to a given date, use the DATE_ADD() function.

MySQL DATE_SUB() Syntax

The syntax of the DATE_SUB() function is as follows:

DATE_SUB(date, INTERVAL expression unit)

Arguments

• The date, expression, and unit are required.
• The unit argument is a type of interval to add. It can be one of the followings:
  • MINUTE
  • HOUR
  • SECOND
  • DAY
  • WEEK
  • QUARTER
  • HOUR_MINUTE
  • HOUR_SECOND
  • MICROSECOND
  • SECOND_MICROSECOND
  • MINUTE_MICROSECOND
  • MINUTE_SECOND
  • HOUR_MICROSECOND
  • DAY_MICROSECOND
  • DAY_SECOND
  • DAY_MINUTE
  • DAY_HOUR
  • YEAR_MONTH

Examples

Subtracting hours

The following statement subtracts 2 hours from “2019-01-01" and returns "2018-12-31 22:00:00":

SELECT DATE_SUB("2019-01-01", INTERVAL 2 HOUR);

Subtracting seconds

The following statement subtracts 3 seconds from "2019-01-01" and returns "2018-12-31 23:59:57":

SELECT DATE_SUB("2019-01-01", INTERVAL 3 SECOND);

Subtracting months

The following statement subtracts three months from "2019-01-01" and returns "2018-10-01":

SELECT DATE_SUB("2019-01-01", INTERVAL 3 MONTH);

Subtracting years

The following statement subtracts one year from "2019-01-01" and returns "2018-01-01":

SELECT DATE_SUB("2019-01-01", INTERVAL 1 YEAR);

Subtracting weeks

The following statement subtracts four weeks from "2019-01-01" and returns "2018-12-04":

SELECT DATE_SUB("2019-01-01", INTERVAL 4 WEEK);

Subtracting hours and minutes

The following statement subtracts 2 hours and 30 minutes from "2019-01-01" and returns "2018-12-31 21:30:00":

SELECT DATE_SUB("2019-01-01", INTERVAL "2:20" HOUR_MINUTE);

Subtracting years and months

The following statement subtracts two years and five months from "2019-01-01" and returns "2016-07-01":

SELECT DATE_SUB("2019-01-01", INTERVAL "1:5" YEAR_MONTH);

In this tutorial, you’ve learned how to use the MySQL DATE_SUB() function. DATE_SUB() is a function that subtracts the time and date interval from a given date and returns a new date. If you want to add the time and date interval to a given date, you can use the DATE_ADD() function.