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 in which we want our output to be.
Below is the syntax of MySQL Date_sub():
DATE_SUB (start_date,time expression unit)
Mathematically, Date_sub function can also be written as below:
The above syntax of date_sub() takes 2 arguments. They are:
- start_date is the initial date or datetime value or beginning date.
- Time expression is a string that is a time value that is to be subtracted from the startdate value. The unit is the time unit as for example, year, month, day etc.
- Unit can be microseconds, seconds, minute, hour, week, month, year, quarter, 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 2 day from the may 24 2020:
Below is the query that subtracts 2 day from 24-may-2020:
Examples of MySQL DATE_SUB()
Below the Examples of MySQL DATE_SUB():
Select date_sub('2020-05-24',interval 2 day) AS result;
In the above example, the start date is 2020-05-24, written in the yyyy-mm-dd format. Second argument is a 2day interval. The output of the date_sub() function is 2020-05-22 in the console.
Below is the query where the time interval is of 5 hours:
Select date_sub('2020-05-24',interval 5 hour) AS result;
Since the time interval is of 5 hours the output if the function is a DateTime value.
Query with a Negative Interval
The time expression in the interval or in the second argument could be positive or negative. We can deal both positive and negative kind of time interval which is passed as a second parameter in the date_sub() function.If the time interval is negative, the date_sub() function works like the date_add() function as illustrated in the below query:
Select date_sub('2020-05-24',interval -2 day) AS result;
In the above query, we have passed a negative 2-day interval as our second parameter. Therefore, in the output,2 day is added in the starting or initial day.
Select date_sub('2020-02-29',interval -2 day) AS result;
In the above query the start date is 2020-02-29 and the second argument i.e. interval is a negative interval of 2 day. Technically, output of the query must be 2020-02-30 which is an invalid date as there is no 30th day in February month. Therefor the output of the query is 2 march 2020.
Query for Invalid or Abnormal Date
Select date_sub('2020-02-35',interval -2 Day) AS result;0
In the below example, 2020-02-35 is an invalid date; therefore, it gives the output as NULL along with a warning message in the output console.
Select date_sub(null,interval 1 Day) AS result;
Since, In the above query, we have passed a null value in the initial date value as our first parameter then whatever we will give as interval in the second argument it will output a null value
Query for Automatic Adjusted Day
If we subtract an interval of MONTH, YEAR, or YEAR_MONTH from a date which outcomes in a date that has a day number bigger 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 in an easy way.
Select date_sub('2020-05-30',interval 1 month) as result;
In this example, we subtracted 1 month from May 24th 2020 so the outcome is April30thApril.
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';
Select date_sub('2020-05-24 11:00:00', interval 6 hour) As result;
Select date_sub(curdate(), interval 6 hour) AS result
select date_sub(curdate(), interval -2 day) result;
Select date_sub('2020-05-24 12:20:20.000010',interval 1000 microsecond) As result;
Select date_sub('2020-05-24 12:20:20.000010',interval 750 microsecond)As result;
- If we specify a time interval value that is too small for the unit that we have specified, the DATE_SUB() will assume that the left-most part of the interval value was not provided by the user.
- DATE_SUB function with a negative value as a parameter is the same as that of using 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 have learned how MySQL date_sub() function is used to subtract the time from a date or datetime value in various units. In this article, we have tried to explain each and every aspect of date_sub() function in an easy way with the query with almost all the units like microseconds, days, months, year, week, quarter etc.along with the screenshots of the output console for the better understanding of the reader. In this article, we have also learned about the negative date-time concepts of date_sub() function.
This is a guide to MySQL DATE_SUB(). Here we discuss the Definition of MySQL DATE_SUB() and the practical examples and different subquery expressions. You can also go through our suggested articles to learn more-