MySQL INTERVAL() function

INTERVAL() function

MySQL INTERVAL() function returns the index of the argument that is more than the first argument.

This function is useful in -

• INTERVAL() can be used to adjust dates by adding or subtracting a specific duration.
• It's particularly useful for dealing with durations, which represent a span of time.
• In analytics and reporting, INTERVAL() is used to group data into time intervals.
• When querying a database for records within a specific date range, INTERVAL() simplifies this process.
• It can assist in handling time zones by allowing you to adjust date and time values relative to a specific time zone.
• When combined with scheduling tools or scripts, INTERVAL() can be used to automate tasks that need to occur at regular intervals.
• In forecasting models and trend analysis, INTERVAL() helps in projecting future events based on historical data.

Syntax:

INTERVAL(N,N1,N2,N3,...)

It returns 0 if 1st number is less than the 2nd number and 1 if 1st number is less than the 3rd number and so on or -1 if 1st number is NULL.

All arguments are treated as an integer.

MySQL Version: 8.0

Example: MySQL INTERVAL() function

The following MySQL statement the 1st number is 85, which is less than 175. All other numbers in the argument are smaller than 85. So, it returns the index of 175. Notice that indexing is started with the 2nd number and first position is zero.

Code:

-- This query uses the INTERVAL function to determine the position of 85 in the sorted list of values (1, 75, 17, 30, 56, 175).
SELECT INTERVAL(85, 1, 75, 17, 30, 56, 175);
-- This statement evaluates the position of 85 in the sorted list and returns the 0-based index of the highest element that is less than or equal to 85.

Explanation:

• The purpose of this SQL query is to determine the position of the number 85 in a sorted list of given values using the INTERVAL function.


• SELECT INTERVAL(85, 1, 75, 17, 30, 56, 175): This part of the query uses the INTERVAL function to find the position of 85 in the list of values (1, 75, 17, 30, 56, 175).


• The INTERVAL function returns the 0-based index of the highest element in the list that is less than or equal to the first argument (in this case, 85).


• The list of values (1, 75, 17, 30, 56, 175) is implicitly sorted by the INTERVAL function before performing the comparison.


• The INTERVAL function will sort the list as (1, 17, 30, 56, 75, 175) and then determine where 85 fits in this sorted list.

Output:

mysql> SELECT INTERVAL(85, 1, 75, 17, 30,56, 175);
+-------------------------------------+
| INTERVAL(85, 1, 75, 17, 30,56, 175) |
+-------------------------------------+
|                                   5 | 
+-------------------------------------+
1 row in set (0.02 sec)

Slideshow of MySQL Comparison Function and Operators

Previous: IN()
Next: IS NOT NULL