Updated May 15, 2023
Definition on MySQL DATE_SUB()
The date_sub() is a built-in function of MySQL database server, which is used to make the difference of a time or date value from a date or DateTime value and outputs the result as date or DateTime. The function accepts two arguments; the first argument passed is starting date or initial date from which the second argument, which is the time interval, is subtracted argument to generate the output. The function can output the result in various units. The unit should be passed in the function we want our output to be.
Syntax:
Below is the syntax of MySQL Date_sub():
DATE_SUB (start_date,time expression unit)Mathematically, the Date_sub function can also be written as below:
Date_sub=difference(start_date,time interval)The above syntax of date_sub() takes two arguments. They are:
- start_date is the initial date or datetime value or beginning date.
- A time expression is a string representing a time value subtracting from the start_date value. The unit is the time unit as year, month, day, etc.
- The unit can be microseconds, seconds, minutes, hours, weeks, months, years, quarters, second_microsecond, minute_microsecond, minute_second, hour_microsecond, hour_second, hour_minute, day_microsecond, day_second, day_minute, day_hour, year_month, etc.
The following statement uses the date_sub () function to subtract two days from May 24, 2020:
Below is the query that subtracts 2 days from 24-may-2020:
Examples of MySQL DATE_SUB()
Below are the examples mentioned for better understanding :
Example #1
Query:
Select date_sub('2020-05-24',interval 2 day) AS result;Output:
The above example’s start date is 2020-05-24, written in the yyyy-mm-dd format. The second argument is a 2-day interval. The output of the date_sub() function is 2020-05-22 in the console.
Example #2
Below is the query where the time interval is of 5 hours:
Query:
Select date_sub('2020-05-24',interval 5 hour) AS result;Output:
Since the time interval is 5 hours, the function’s output is a DateTime value.
Query with a Negative Interval
The interval or second argument’s time expression could be positive or negative. We can handle both positive and negative time intervals passed as the second parameter in the date_sub() function. When the time interval is negative, the date_sub() function functions similarly to the date_add() function, as demonstrated in the query below:
Example #3
Query:
Select date_sub('2020-05-24',interval -2 day) AS result;Output:
In the above query, we have passed a negative 2-day interval as our second parameter. Therefore, two days are added to the starting or initial days in the output.
Example #4
Query:
Select date_sub('2020-02-29',interval -2 day) AS result;Output:
In the above query, the start date is 2020-02-29, and the second argument, i.e., the interval is a negative interval of 2 days. Technically, the output of the query must be 2020-02-30, which is an invalid date as there is no 30th day in February month. Therefore the output of the query is March 2nd, 2020.
Query for an Invalid or Abnormal Date
Example #5
Query:
Select date_sub('2020-02-35',interval -2 Day) AS result;0Output:
In the example below, 2020-02-35 is an invalid date; therefore, it gives the output NULL and a warning message in the output console.
Example #6
Query:
Show warnings;Output:
Example #7
Query:
Select date_sub(null,interval 1 Day) AS result;Output:
Since, In the above query, we have passed a null value in the initial date value as our first parameter, whatever we give as an interval in the second argument will output a null value.
Query for Automatic Adjusted Day
When subtracting a MONTH, YEAR, or YEAR_MONTH interval from a date, if the result is a date with a day number greater than the maximum day of the new month, the day will be adjusted to the maximum day in the new month.
The below query explains the automatically adjusted day concept easily.
Example #8
Query:
Select date_sub('2020-05-30',interval 1 month) as result;Output:
In this example, we subtracted one month from May 24th, 2020, so the outcome is April 30th, April.
Example #9
Query:
Select Date_sub('2020-05-24',interval 2 week) AS 'negative week';
Select Date_sub('2020-05-24', interval 2 month) AS 'negative month';
Select Date_sub('2020-05-24',interval 2 quarter) AS 'negative quarter';Output:
Example #10
Query:
Select date_sub('2020-05-24 11:00:00', interval 6 hour) As result;Output:
Example #11
Query:
Select date_sub(curdate(), interval 6 hour) AS resultOutput:
Example #12
Query:
select date_sub(curdate(), interval -2 day) result;Output:
Example #13
Query:
Select date_sub('2020-05-24 12:20:20.000010',interval 1000 microsecond) As result;Output:
Example #14
Query:
Select date_sub('2020-05-24 12:20:20.000010',interval 750 microsecond)As result;Output:
Note:
- If we specify a time interval value that is too small for the specified unit, the DATE_SUB() will assume that the user did not provide the left-most part of the interval value.
- DATE_SUB function with a negative value as a parameter is the same as that of the DATE_ADD function.
The DATE_SUB function is used in the below-mentioned versions of MySQL:
- MySQL 5.7, MySQL 5.6, MySQL 5.5, MySQL 5.1, MySQL 5.0, MySQL 4.1, MySQL 4.0, MySQL 3.23
Conclusion – MySQL DATE_SUB()
In this article, we learned how MySQL date_sub() function subtracts the time from a date or datetime value in various units. We have tried to explain every aspect of the date_sub() function easily with the query with almost all the units like microseconds, days, months, years, weeks, quarters, etc., along with the screenshots of the output console for a better understanding of the reader. We have also learned about the negative date-time concepts of the date_sub() function.
Recommended Articles
We hope that this EDUCBA information on “MySQL DATE_SUB()” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
