EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials MySQL Tutorial MySQL Interval
 

MySQL Interval

Priya Pedamkar
Article byPriya Pedamkar

Updated May 24, 2023

MySQL-Interval

 

 

Introduction to MySQL Interval

The MySQL Interval Operator uses the binary search algorithm to find the items from the list and returns the values in range 0 to N. It can also be said that it returns the index of the argument, which is greater than the first argument passed in the interval function.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

It outputs 0(zeroth index) if 1st number is less than the 2nd number and 1(index 1) if 1st number is less than the 3rd number like this, it goes on and checks for the last element in the function, and if the first element is null, it will return -1 as an output.

All elements or arguments passed in the function acts as an integer.

Syntax

Below is the syntax of MySQL interval function

select interval(n1,n2, n3…..nn)

If n<n1, then it returns 0. If n<n2, then it will return 1, n<n3, it will return 2, and so on. All the arguments must be in ascending order to function like n1<n2>n3……nn. Otherwise, it will not work properly.

Interval Function

The table below describes the unit associated with the interval function’s expression.

unit  Expression 
DAY DAYS
DAY_HOUR ‘DAYS HOURS’
DAY_MICROSECOND ‘DAYS HOURS:MINUTES:SECONDS. MICROSECONDS’
DAY_MINUTE ‘DAYS HOURS:MINUTES’
DAY_SECOND ‘DAYS HOURS:MINUTES:SECONDS’
HOUR HOURS
HOUR_MICROSECOND ‘HOURS:MINUTES:SECONDS.MICROSECONDS’
HOUR_MINUTE ‘HOURS:MINUTES’
HOUR_SECOND ‘HOURS:MINUTES:SECONDS’
MICROSECOND MICROSECONDS
MINUTE MINUTES
MINUTE_MICROSECOND ‘MINUTES:SECONDS.MICROSECONDS’
MINUTE_SECOND ‘MINUTES:SECONDS’
MONTH MONTHS
QUARTER QUARTERS
SECOND SECONDS
SECOND_MICROSECOND ‘SECONDS.MICROSECONDS’
WEEK WEEKS
YEAR YEARS
YEAR_MONTH ‘YEARS-MONTHS’

Examples to Implement MySQL Interval

Below are the examples of implementing MySQL Interval:

Example #1

Code:

select interval(45,10,20,30,40,50,60,70);

Output:

interval

Example #2

Code:

select interval(12,10,20,30,40,50,60,70);

Output:

MySQL Interval - 2

Example #3

Code:

select interval(67,10,20,30,40,50,60,70);

Output:

MySQL Interval - 3

Example #4

Code:

select interval(6,10,20,30,40,50,60,70);

Output:

MySQL Interval - 4

Example #5

Code:

select interval(100,10,20,30,40,50,60,70);

Output:

MySQL Interval - 5

Example #6

Code:

select interval(null,10,20,30,40,50,60,70);

Output: Since the first argument is null, the above query returns -1 as an output in the output console.

console

MySQL interval operator is mainly used for date and time calculations. The below expression is used to create an interval:

Syntax:

INTERVAL expression unit

The expression defines the interval value, and the unit determines the unit of the interval.

Example: interval 5 day

The above example explains how 5 days are created.

  • Date + interval expression unit
  • Date + interval expression unit

Generally, the interval values are calculated with date, time,date_add,date_subfunctions, etc.

The below statement adds four days to June 4, 2020that returns on June 8, 2020:

Example #7

Code:

select '2020-06-04' + interval 4 day;

Output:

interval value

When an interval value is used in a date, and datetime expression that involved value and the interval value will be on the right-hand side of the expression, we can use the negative date and date-time value in the expression as shown in the below example:

Example #8

Code:

select '2020-06-04' + interval -5 day;

Output:

MySQL Interval - 8

Example #9

Code:

select date_add('2020-06-04' , interval 2 month), date_sub('2020-06-04' , interval 2 month);

In the above query, two months are added on 2020-06-04 with the date_add() function, and then two months are subtracted from 2020-06-04 with the date_sub() function.

Output:

date_sub()

Example #10

Code:

select timestampadd(minute,45,'2020-06-04');

In the above query, timestampadd function uses three arguments unit, interval, and expression, which adds 45 minutes in the given date: 2020-06-04

Output:

MySQL Interval - 10

Mysql interval keyword is also used with now() and curdate()

Below is the query that explains how the interval is used with the now() and curdate() function

Example #11

Code:

select now() + interval 3 day;

In the above query, three days are added in the now function. Now function gives us the date plus the current time of the system.

Output:

MySQL Interval - 11

Example #12

Code:

select now() - interval 3 day;

In the above query, 3 days are subtracted from now.

Output:

MySQL Interval - 12

Example #13

Code:

select now() - interval 4 hour;

In the above query, 4 hours is subtracted from the current date and time. Therefore, the output will be in date and time format.

Output:

date and time

Example #14

Code:

select curdate()-interval 3 day;

In the above query, 3 days is subtracted from the current date. Therefore, the output will be only the 3 day less than the current date.

Output:

subtracted

Example #15

Code:

select curdate() + interval 3 day;

Output:

curdate()

Conclusion

In this article, we have learned about the interval function. We have also learned about how it is used with different MySQL functions like the date(), time(), date_add(), date_sub(), now(), curdate(), etc. In this article, we have tried to cover every concept of interval easily. In this article, all the concepts of the interval are explained with MySQL query along with the screenshot of the output console to provide a better understanding to the reader.

Recommended Articles

We hope that this EDUCBA information on “MySQL Interval” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. What is MySQL Schema?
  2. Wildcards in MySQL
  3. MySQL Constraints
  4. Cheat Sheet MySQL

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA

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

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

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 Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW