MySQL Subtract Days, Hour, Minute from Date or DateTime

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 ValueExpected expr Format
MICROSECONDMICROSECONDS
SECONDSECONDS
MINUTEMINUTES
HOURHOURS
DAYDAYS
WEEKWEEKS
MONTHMONTHS
QUARTERQUARTERS
YEARYEARS
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.

Leave a Comment