MySQL SUBDATE() Function

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

The SUBDATE() function in MySQL is used to subtract a specified time or date interval from a given date and return the resulting date.

Definition and Usage

  • Purpose: Subtracts a time/date interval (e.g., days, months, years) from a date and returns the resulting date.
  • This function can work with both simple day subtraction and more complex time intervals like months or years.

Syntax

1. Subtract a specific interval from a date

SUBDATE(date, INTERVAL value unit)

2. Subtract a number of days from a date

SUBDATE(date, days)

Parameters

  1. date: Required. The original date from which the interval will be subtracted.
  2. value: Required. The value of the interval to subtract (e.g., 10, 3, 5).
  3. unit: Required when using the INTERVAL keyword. The type of interval (e.g., DAY, MONTH, YEAR, etc.).
  4. days: In the second syntax, this represents the number of days to subtract from the date.

Interval Units

  • 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

Technical Details

  • Works in: MySQL 4.0 and later
  • Return Type: DATE or DATETIME depending on the input.
  • Behavior: The result will be the original date minus the specified time/date interval.

Examples

Example 1: Subtract 10 days from a date

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

Result: 2017-06-05
Explanation: This subtracts 10 days from the date 2017-06-15, resulting in 2017-06-05.

Example 2: Subtract 15 minutes from a datetime

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

Result: 2017-06-15 09:19:21
Explanation: This subtracts 15 minutes from 2017-06-15 09:34:21, resulting in 2017-06-15 09:19:21.

Example 3: Subtract 3 hours from a datetime

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

Result: 2017-06-15 06:34:21
Explanation: This subtracts 3 hours from 2017-06-15 09:34:21, resulting in 2017-06-15 06:34:21.

Example 4: Add 2 months to a date (subtracting a negative interval)

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

Result: 2017-08-15
Explanation: This adds 2 months to the date 2017-06-15, resulting in 2017-08-15. (Note: Subtracting a negative value is equivalent to adding the interval.)

Use Cases

  • Calculating Past Dates: To find dates in the past based on a given date, such as calculating the due date for a task or the date a project started.
  • Date Manipulation: Useful for operations where you need to subtract certain time periods (days, months, years) from a given date, such as calculating expiration dates or age.
  • Handling Time Intervals: Works well with both simple day subtraction and more complex time intervals like months or years.

Key Notes

  • Ensure that the interval unit (DAY, MONTH, etc.) is correctly specified when using the INTERVAL keyword.
  • This function can handle both negative values (for adding intervals) and positive values (for subtracting intervals), making it flexible for various date manipulations.
Was this article helpful?