MySQL DATE_SUB() Function

 The DATE_SUB() function in MySQL is used to subtract a specific time interval from a date and return the resulting date. It’s particularly useful when you want to calculate dates relative to a given starting point.

Syntax

DATE_SUB(date, INTERVAL expr unit)

Parameters:

• date: The starting date from which the interval will be subtracted.
• expr: The quantity of the interval to subtract (e.g., 5, 10, etc.).
• unit: The unit of the interval to subtract (e.g., DAY, MONTH, YEAR, etc.).

Supported 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

Example Queries

1. Subtracting Days

SELECT DATE_SUB('2024-12-23', INTERVAL 10 DAY) AS Result;

Output: 2024-12-13

2. Subtracting Months

SELECT DATE_SUB('2024-12-23', INTERVAL 2 MONTH) AS Result;

Output: 2024-10-23

3. Subtracting Years

SELECT DATE_SUB('2024-12-23', INTERVAL 1 YEAR) AS Result;

Output: 2023-12-23

4. Using Current Date

SELECT DATE_SUB(CURDATE(), INTERVAL 7 DAY) AS OneWeekAgo;

This will return the date one week before the current date.

5. Combining Units

SELECT DATE_SUB('2024-12-23 15:30:45', INTERVAL 1 HOUR) AS Result;

Output: 2024-12-23 14:30:45

Notes:

• If you pass an invalid date, the function returns NULL.
• Use this function for time-based calculations, reporting, or historical data manipulation.

Would you like help with a specific use case?