MySQL DATE_SUB() function; In this tutorial, i am going to show you how to subtract days, week, month, year, hours, minutes from date or datetime using MySQL date_sub() function.
MySQL DATE_SUB() Function
DATE_SUB() function in MySQL is used to subtract a specified time or date interval to a specified date and then returns the date.
Syntax of MySQL DATE_SUB() Function
The basic syntax of MySQL DATE_SUB() function is:
DATE_SUB(date,INTERVAL expr unit)
Example 1 – MySQL DATE_SUB() Function
Let’s take an first simple example using MySQL date_sub() function; as follows:
SELECT DATE_SUB('2019-07-23', INTERVAL 6 DAY) AS Result;
Output-1
+------------+ | Result | +------------+ | 2019-07-17 | +------------+
Example 2 – MySQL DATE_SUB() with CURDATE() Function
Let’s take an example to subtract the days, weeks, months, years’ in the current date using curdate() with date_sub function; as follows:
SELECT CURDATE() AS 'Start Date', DATE_SUB(CURDATE(), INTERVAL 2 WEEK) AS '-2 Weeks', DATE_SUB(CURDATE(), INTERVAL 2 MONTH) AS '-2 Months', DATE_SUB(CURDATE(), INTERVAL 2 QUARTER) AS '-2 Quarters', DATE_SUB(CURDATE(), INTERVAL 2 YEAR) AS '-2 Years';
Output-2
+------------+------------+------------+-------------+------------+ | Start Date | -2 Weeks | -2 Months | -2 Quarters | -2 Years | +------------+------------+------------+-------------+------------+ | 2019-07-23 | 2019-07-09 | 2019-05-23 | 2019-01-23 |2017-07-23 | +------------+------------+------------+-------------+------------+
Example 3 – MySQL Subtract Hours from Date or DateTime
To subtract the time units from the given date/time value and also subtract the time unit from the now() function; as follows:
SELECT DATE_SUB('2019-07-21 10:00:00', INTERVAL 4 HOUR) AS Result, DATE_SUB(NOW(), INTERVAL 5 HOUR) AS NOW;
Output-3
+---------------------+---------------------+ | Result | NOW | +---------------------+---------------------+ | 2019-07-23 06:00:00 | 2019-07-22 22:04:40 | +---------------------+---------------------+
Let’s see the following valid unit values and their expected format; as follows:
unit Value | Expected expr Format |
---|---|
MICROSECOND | MICROSECONDS |
SECOND | SECONDS |
MINUTE | MINUTES |
HOUR | HOURS |
DAY | DAYS |
WEEK | WEEKS |
MONTH | MONTHS |
QUARTER | QUARTERS |
YEAR | YEARS |
SECOND_MICROSECOND | ‘SECONDS.MICROSECONDS’ |
MINUTE_MICROSECOND | ‘MINUTES:SECONDS.MICROSECONDS’ |
MINUTE_SECOND | ‘MINUTES:SECONDS’ |
HOUR_MICROSECOND | ‘HOURS:MINUTES:SECONDS.MICROSECONDS’ |
HOUR_SECOND | ‘HOURS:MINUTES:SECONDS’ |
HOUR_MINUTE | ‘HOURS:MINUTES’ |
DAY_MICROSECOND | ‘DAYS HOURS:MINUTES:SECONDS.MICROSECONDS’ |
DAY_SECOND | ‘DAYS HOURS:MINUTES:SECONDS’ |
DAY_MINUTE | ‘DAYS HOURS:MINUTES’ |
DAY_HOUR | ‘DAYS HOURS’ |
YEAR_MONTH | ‘YEARS-MONTHS’ |
Conclusion
MySQL DATE_SUB() Function, you have learned how to use MySQL date_sub() function with various examples.