DATE_SUB() Function Examples – MySQL Tutorial

DATE_SUB() Function Examples – MySQL Tutorial

In this tutorial, we would love to share with you how to use the date_sub() function of MySQL. How it works the function, we will take various examples for demonstration.

MySQL DATE_SUB() Function

In MySQL,  the DATE_SUB () function is used to subtract(decrease) a specified time from a given date. 

Example:- You want to subtract(decrease) 15 days from a given date. You can also to subtract day, week, month, quarter, year from a given date. You can also subtract(decrease) a time value, such as seconds, microseconds, etc.

Syntax

The basic syntax of MySQL DATE_SUB() function is:

DATE_SUB(date,INTERVAL expr unit)

Example-1

Here, we will take the first simple example for demonstration of this function is:

SELECT DATE_SUB('2019-07-23', INTERVAL 6 DAY) AS Result;

Output-1

+------------+
| Result     |
+------------+
| 2019-07-17 |
+------------+

In the above example, we have subtracted the 6 days from the given date.

Example-2

We take the next example of this function with curdate() function of MySQL. In this example, we will subtract the days, weeks, months, years’ in the current date.

 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

Next, we take an example of this function with MySQL NOW() function. We will subtract the time units from the given date/time value and also subtract the time unit from the now() function. See the example below for a demonstration:

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 |
 +---------------------+---------------------+

Expected Values

The following table shows the valid unit values and their expected format.

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

Here, you have learned how to use MySQL date_sub() function with various examples.

If you have any questions or thoughts to share, use the comment form below to reach us.

Leave a Reply

Your email address will not be published. Required fields are marked *