MySQL SUBDATE() Function
The SUBDATE() function in MySQL allows you to subtract a specified time interval from a date value. This is a very useful function for calculating past dates or for analyzing data based on time periods.
SUBDATE(): Definition and Usage
SUBDATE() lets you easily adjust dates backward. You can subtract various time units such as days, months, years, hours, minutes, seconds, etc. You can also add time by using a negative value for the interval.
Syntax
Syntax
SUBDATE(date, INTERVAL value unit)
--or the simplified version for subtracting days only--
SUBDATE(date, days)
Parameter Values
| Parameter | Description |
|---|---|
date |
The starting date or datetime value. This is required. |
value |
The numerical value representing the amount of the time interval to subtract (positive) or add (negative). This is required. |
unit |
The unit of the time interval. Options: 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. This is required. |
days |
The number of days to subtract (positive) or add (negative). Used only in the simplified syntax. This is required when using the simplified syntax. |
Examples
Subtracting Days
Subtracting 10 days from June 15th, 2017.
Syntax
SELECT SUBDATE("2017-06-15", INTERVAL 10 DAY);
Output
2017-06-05
Subtracting Minutes
Subtracting 15 minutes from a datetime value.
Syntax
SELECT SUBDATE("2017-06-15 09:34:21", INTERVAL 15 MINUTE);
Output
2017-06-15 09:19:21
Subtracting Hours
Subtracting 3 hours from a datetime value.
Syntax
SELECT SUBDATE("2017-06-15 09:34:21", INTERVAL 3 HOUR);
Output
2017-06-15 06:34:21
Adding Months
Adding 2 months to a date (by subtracting a negative interval).
Syntax
SELECT SUBDATE("2017-06-15", INTERVAL -2 MONTH);
Output
2017-08-15