MySQL DATE_SUB() Function

  1. Overview
  2. MYSQL
  3. Date Functions
  4. MySQL DATE_SUB() Function

The DATE_SUB() function in MySQL is used to subtract a time/date interval from a given date and return the result. This is useful when you need to compute a date that is a certain number of days, months, or other units before or after a given date.

Definition and Usage

  • The DATE_SUB() function subtracts a specified interval from a date or datetime value.

Syntax

DATE_SUB(date, INTERVAL value interval)
  • date: The date to be modified.
  • value: The value of the time/date interval to subtract (both positive and negative values are allowed).
  • interval: The type of interval to subtract. This can be one of the following values:
    • MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR
    • SECOND_MICROSECOND, MINUTE_MICROSECOND, MINUTE_SECOND, HOUR_MICROSECOND, HOUR_SECOND, HOUR_MINUTE, DAY_MICROSECOND, DAY_SECOND, DAY_MINUTE, DAY_HOUR, YEAR_MONTH

Examples

Example 1: Subtract 10 days from a date

SELECT DATE_SUB("2017-06-15", INTERVAL 10 DAY);

Output:

2017-06-05

This query subtracts 10 days from the date 2017-06-15 and returns 2017-06-05.

Example 2: Subtract 15 minutes from a datetime

SELECT DATE_SUB("2017-06-15 09:34:21", INTERVAL 15 MINUTE);

Output:

2017-06-15 09:19:21

This query subtracts 15 minutes from the datetime 2017-06-15 09:34:21 and returns 2017-06-15 09:19:21.

Example 3: Subtract 3 hours from a datetime

SELECT DATE_SUB("2017-06-15 09:34:21", INTERVAL 3 HOUR);

Output:

2017-06-15 06:34:21

This query subtracts 3 hours from the datetime 2017-06-15 09:34:21 and returns 2017-06-15 06:34:21.

Example 4: Add 2 months to a date (using negative value)

SELECT DATE_SUB("2017-06-15", INTERVAL -2 MONTH);

Output:

2017-08-15

This query effectively adds 2 months to the date 2017-06-15 by using a negative interval value, resulting in 2017-08-15.

Use Cases

  • Date Calculations: Subtracting days, months, or years from a given date for calculations or reporting.
  • Time Interval Adjustments: Adjusting times (e.g., subtracting hours or minutes) in scheduling or logging applications.
  • Flexible Date Handling: Using negative intervals to add time to a date.

Related Functions

  • DATE_ADD(): Adds a time/date interval to a date.
  • NOW(): Returns the current date and time.
  • CURDATE(): Returns the current date.
  • DATE_FORMAT(): Formats a date according to a specified format.
Was this article helpful?