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.

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<n₁ then it returns 0. If n<n₂ 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 n₁<n₂>n₃……n_n. 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 –

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