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.
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 and like this, it goes on and check for the last element in 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 make them function like n1<n2>n3……nn. Otherwise, it will not work properly.
Interval Function
Below table describes the unit that is associated with the expression of the interval function.
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 to implement MySQL Interval:
Example #1
Code:
select interval(45,10,20,30,40,50,60,70);
Output:
Example #2
Code:
select interval(12,10,20,30,40,50,60,70);
Output:
Example #3
Code:
select interval(67,10,20,30,40,50,60,70);
Output:
Example #4
Code:
select interval(6,10,20,30,40,50,60,70);
Output:
Example #5
Code:
select interval(100,10,20,30,40,50,60,70);
Output:
Example #6
Code:
select interval(null,10,20,30,40,50,60,70);
Output: In the above query, since the first argument is null, it returns -1 as an output in the output console.
MySQL interval operator is mostly used for date and time calculations. 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
Above example explained how Interval of 5 day is created.
- Date + interval expression unit
- Date + interval expression unit
Generally the interval values are calculated with date,time,date_add,date_subfunctions etc.
Below statement adds 4 days to 4 June 2020that returns June 8 2020:
Example #7
Code:
select '2020-06-04' + interval 4 day;
Output:
When an interval value is used in a date and datetime expression that involved value and the interval value will 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:
Example #9
Code:
select date_add('2020-06-04' , interval 2 month), date_sub('2020-06-04' , interval 2 month);
In the above query 2 month is added in 2020-06-04 with date_add() function and then 2 month is subtracted from 2020-06-04 with date_sub() function.
Output:
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 keyword is also used with now () and curdate()
Below is the query that explains how the interval is used with now() and curdate() function
Example #11
Code:
select now() + interval 3 day;
In the above query 3 days is added in the now function. Now function gives us date plus the current time of the system.
Output:
Example #12
Code:
select now() - interval 3 day;
In the above query, 3 day is subtracted from now.
Output:
Example #13
Code:
select now() - interval 4 hour;
In the above query, 4 hour is subtracted from the current date and time. Therefore, the output will be in date and time format.
Output:
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:
Example #15
Code:
select curdate() + interval 3 day;
Output:
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 date() ,time(), date_add(), date_sub(), now(), curdate() etc. In this article, we have tried to cover each and every concept of interval in an easy way. 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
This is a guide to MySQL Interval. Here we discuss an introduction, syntax, table of interval function, and examples to implement MySQL Interval. You can also go through our other related articles to learn more –
12 Online Courses | 10 Hands-on Projects | 92+ Hours | Verifiable Certificate of Completion
4.5
View Course
Related Courses