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 then returns a new date.

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-10 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 to subtract a time and date interval from a given date and then return a new date. If you need to add a time and date interval to a given date, then you can use the DATE_ADD() function.

Leave a Reply

Your email address will not be published. Required fields are marked *