EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL DATE_SUB()

MySQL DATE_SUB()

Priya Pedamkar
Article byPriya Pedamkar

Updated May 15, 2023

MySQL-DATE_SUB-1-3

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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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:

MySQL-DATE_SUB-1

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:

MySQL-DATE_SUB-2

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:

MySQL-DATE_SUB-3

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:

MySQL-DATE_SUB-4

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;0

Output:

Example-5

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

Example #7

Query:

Select date_sub(null,interval 1 Day) AS result;

Output:

NULL-Result-example7

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:

NULL-Result-1-example8

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:

NULL-Result-2-example9

Example #10

Query:

Select date_sub('2020-05-24 11:00:00', interval 6 hour) As result;

Output:

NULL-Result-3-example10

Example #11

Query:

Select date_sub(curdate(), interval 6 hour) AS result

Output:

Result-example11

Example #12

Query:

select date_sub(curdate(), interval -2 day) result;

Output:

Example-12

Example #13

Query:

Select date_sub('2020-05-24 12:20:20.000010',interval 1000 microsecond) As result;

Output:

Example-13

Example #14

Query:

Select date_sub('2020-05-24 12:20:20.000010',interval 750 microsecond)As result;

Output:

Example-14

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.

  1. MySQL Database Repair
  2. Working on MySQL Timestamp
  3. MySQL IN Operator
  4. MySQL Subquery
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
Popular Course in this category
MYSQL Course Bundle - 18 Courses in 1 | 3 Mock Tests
 93+ Hour of HD Videos
18 Courses
3 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more