MySQL DATE_SUB Function

MySQL DATE_SUB() Function

The DATE_SUB() function in MySQL is used to subtract an interval of time from a specified date or datetime value. It’s particularly useful when working with date calculations, such as finding dates in the past relative to a given date.

Syntax

DATE_SUB(date, INTERVAL expr unit)

date: The starting date or datetime value from which the interval will be subtracted.
INTERVAL expr unit: Specifies the amount and type of time to subtract.
- expr: A number representing the interval value.
- unit: The unit of time (e.g., DAY, MONTH, YEAR).

Supported Units

Unit	Description
`MICROSECOND`	Microseconds
`SECOND`	Seconds
`MINUTE`	Minutes
`HOUR`	Hours
`DAY`	Days
`WEEK`	Weeks
`MONTH`	Months
`QUARTER`	Quarters (3 months)
`YEAR`	Years

Examples

1. Subtracting Days from a Date

To subtract 7 days from 2025-01-12:

SELECT DATE_SUB('2025-01-12', INTERVAL 7 DAY) AS result;

Output:

2025-01-05

2. Subtracting Months from a Date

To subtract 2 months from 2025-01-12:

SELECT DATE_SUB('2025-01-12', INTERVAL 2 MONTH) AS result;

Output:

2024-11-12

3. Subtracting Years from a Date

To subtract 5 years from 2025-01-12:

SELECT DATE_SUB('2025-01-12', INTERVAL 5 YEAR) AS result;

Output:

2020-01-12

4. Subtracting Time from a Datetime

To subtract 3 hours and 15 minutes from a datetime value 2025-01-12 15:30:00:

SELECT DATE_SUB('2025-01-12 15:30:00', INTERVAL 3 HOUR) AS result;
SELECT DATE_SUB('2025-01-12 15:30:00', INTERVAL 15 MINUTE) AS result;

Output:

2025-01-12 12:30:00 (for 3 hours)
2025-01-12 15:15:00 (for 15 minutes)

5. Combining Multiple Subtractions

You can nest DATE_SUB() functions to perform multiple subtractions. For example, to subtract 1 year and 2 months from 2025-01-12:

SELECT DATE_SUB(DATE_SUB('2025-01-12', INTERVAL 1 YEAR), INTERVAL 2 MONTH) AS result;

Output:

2023-11-12

Practical Applications

Generating Past Date Reports: Use DATE_SUB() to filter records older than a specific time.
```
SELECT * FROM sales WHERE sale_date >= DATE_SUB(NOW(), INTERVAL 30 DAY);
```

Calculating Expiry Dates: Subtract trial periods to find expiration dates.

SELECT DATE_SUB(start_date, INTERVAL 14 DAY) AS trial_expiry FROM users;

Analyzing Historical Data: Subtract years or months to compare historical data trends.

SELECT * FROM orders WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR);

Best Practices

Validate Input: Ensure the date parameter is valid to avoid unexpected results.
Use CURDATE() or NOW(): For dynamic date subtraction, use built-in functions like CURDATE() (for date) or NOW() (for datetime).
Optimize Queries: When using DATE_SUB() in WHERE clauses, ensure the column used is indexed for better performance.

Conclusion

The a DATE_SUB() function is a powerful tool for date manipulation in MySQL. It allows you to subtract intervals of time from a given date or datetime, making it ideal for dynamic calculations in applications such as reports, audits, and data analysis. By understanding its syntax and capabilities, you can effectively handle date-based operations in your database.

Souy Soeng

MySQL DATE_SUB Function