MySQL DATE_SUB And SUBDATE Functions: Date Subtraction

In MySQL, the DATE_SUB() and SUBDATE() functions are used to subtract a time/date interval from a date and then return the date. These functions are useful when you need to calculate a past date from a given date.

Here's the basic syntax for DATE_SUB():

DATE_SUB(date, INTERVAL expr type)

And for SUBDATE():

SUBDATE(date, INTERVAL expr type)

Or alternatively:

SUBDATE(date, days)

In both cases, date is the starting date, expr is the amount of interval to subtract, and type can be one of the following values: SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.

Here's an example using DATE_SUB():

SELECT DATE_SUB('2023-01-01', INTERVAL 1 DAY);

This will return '2022-12-31', which is one day before the original date.

Here's an example using SUBDATE() with the INTERVAL keyword:

SELECT SUBDATE('2023-01-01', INTERVAL 1 DAY);

This will also return '2022-12-31'.

You can also use SUBDATE() without the INTERVAL keyword, in which case it subtracts the specified number of days:

SELECT SUBDATE('2023-01-01', 1);

This will return '2022-12-31' as well.

These functions can be used in a WHERE clause to find data in a date range, in a SELECT clause to output calculated dates, or anywhere you need to calculate a date or datetime based on an interval.

How to Use DATE_SUB in MySQL:
- Description: The DATE_SUB function is used to subtract a specified time interval from a date or datetime value.
- Code:
```
-- Example of using DATE_SUB
SELECT DATE_SUB(NOW(), INTERVAL 1 DAY) AS past_date;
```
Subtracting Days from a Date in MySQL using DATE_SUB:
- Description: Subtract a specific number of days from a date using the DAY interval.
- Code:
```
-- Example subtracting 7 days from a date
SELECT DATE_SUB('2023-01-15', INTERVAL 7 DAY) AS past_date;
```
Subtracting Months from a Date with DATE_SUB in MySQL:
- Description: Use the MONTH interval to subtract months from a date.
- Code:
```
-- Example subtracting 3 months from a date
SELECT DATE_SUB('2023-01-01', INTERVAL 3 MONTH) AS past_date;
```
Subtracting Hours, Minutes, and Seconds from a Date in MySQL:
- Description: Adjust the time part of a datetime value by subtracting hours, minutes, and seconds.
- Code:
```
-- Example subtracting 2 hours and 30 minutes from a datetime
SELECT DATE_SUB('2023-01-01 12:00:00', INTERVAL 2 HOUR + 30 MINUTE) AS new_time;
```
Subtracting Weeks from a Date with DATE_SUB in MySQL:
- Description: Use the WEEK interval to subtract weeks from a date.
- Code:
```
-- Example subtracting 2 weeks from a date
SELECT DATE_SUB('2023-01-15', INTERVAL 2 WEEK) AS past_date;
```

MySQL DATE_SUB Function in WHERE Clause:

Description: Apply DATE_SUB in the WHERE clause to filter records based on date conditions.

Code:

-- Example selecting records with a date within the last 7 days
SELECT * FROM events WHERE event_date >= DATE_SUB(NOW(), INTERVAL 7 DAY);

Subtracting Years from a Date with SUBDATE in MySQL:
- Description: Use the YEAR interval to subtract years from a date. SUBDATE is an alternative to DATE_SUB for this purpose.
- Code:
```
-- Example subtracting 5 years from a date using SUBDATE
SELECT SUBDATE('2023-01-01', INTERVAL 5 YEAR) AS past_date_sub;
```
Handling Negative Intervals with DATE_SUB in MySQL:
- Description: Negative intervals can be used with DATE_SUB to add time to a date.
- Code:
```
-- Example adding 3 days to a date using negative interval
SELECT DATE_SUB('2023-01-01', INTERVAL -3 DAY) AS future_date;
```