How the INTERVAL() function works in Mariadb?

The MariaDB INTERVAL() function is used to return the index of the argument that is less than the first argument. It is often utilized in sorting and searching operations where the position of an element within a sorted list is required.

Syntax

MariaDB INTERVAL() function’s syntax is as follows:

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

• N is the number to compare against the following arguments.
• N1, N2, N3, … are the numbers that are compared to N.

The function returns 0 if N is less than N1, otherwise it returns the index of the argument that is less than or equal to N.

Examples

Example 1: Basic Usage

This example demonstrates the basic usage of the INTERVAL() function.

SELECT INTERVAL(23, 10, 20, 30, 40, 50);

The output for this statement is:

+----------------------------------+
| INTERVAL(23, 10, 20, 30, 40, 50) |
+----------------------------------+
|                                2 |
+----------------------------------+

This result indicates that 23 is less than 30 but not less than 20, so the index returned is 2.

Example 2: With Equal Value

Illustrating the behavior when N is equal to one of the arguments.

SELECT INTERVAL(30, 10, 20, 30, 40, 50);

The output for this statement is:

+----------------------------------+
| INTERVAL(30, 10, 20, 30, 40, 50) |
+----------------------------------+
|                                3 |
+----------------------------------+

Since 30 is equal to the third argument, the index returned is 3.

Example 3: First Argument is the Smallest

Showing the result when N is smaller than any of the other arguments.

SELECT INTERVAL(5, 10, 20, 30, 40, 50);

The output for this statement is:

+---------------------------------+
| INTERVAL(5, 10, 20, 30, 40, 50) |
+---------------------------------+
|                               0 |
+---------------------------------+

The function returns 0 because 5 is less than the first argument (10).

Example 4: Last Argument is the Smallest

Demonstrating the result when N is greater than all other arguments.

SELECT INTERVAL(55, 10, 20, 30, 40, 50);

The output for this statement is:

+----------------------------------+
| INTERVAL(55, 10, 20, 30, 40, 50) |
+----------------------------------+
|                                5 |
+----------------------------------+

The function returns 5 because 55 is greater than all the listed arguments.

Example 5: Using INTERVAL() with Table Data

Using INTERVAL() to find the position of a value within a range stored in a table.

DROP TABLE IF EXISTS numbers;
CREATE TABLE numbers (value INT);
INSERT INTO numbers (value) VALUES (10), (20), (30), (40), (50);

SELECT value, INTERVAL(value, 10, 20, 30, 40, 50) AS position FROM numbers;

The output for this statement is:

+-------+----------+
| value | position |
+-------+----------+
|    10 |        1 |
|    20 |        2 |
|    30 |        3 |
|    40 |        4 |
|    50 |        5 |
+-------+----------+

This table shows the values and their positions within the range.

Related Functions

Below are a few functions related to the MariaDB INTERVAL() function:

• MariaDB FIND_IN_SET() function is used to find the position of a string within a list of strings separated by commas.
• MariaDB FIELD() function is used to return the index of a value in a list of values.

Conclusion

The INTERVAL() function in MariaDB is a useful tool for determining the position of a number within a set of numbers. It is particularly helpful in scenarios where the relative position of an element is needed for comparison or sorting purposes. Understanding how to use the INTERVAL() function can greatly enhance the efficiency of database operations that involve numerical data analysis.