INTERVAL()

INTERVAL(N, N1, N2, N3, …) returns the position of the last threshold value that is less than or equal to N, using a fast binary search. It returns 0 if N < N1, -1 if N is NULL, and skips NULL thresholds during comparison.

Description

INTERVAL(N, N1, N2, N3, ...) compares the value N against a sorted list of threshold values N1, N2, N3, ... and returns the index of the largest threshold that is less than or equal to N. Specifically:

  • Returns 0 if N < N1.

  • Returns 1 if N1 <= N < N2.

  • Returns 2 if N2 <= N < N3.

  • And so on, up to the total number of thresholds.

All arguments are treated as integers. The thresholds must be in non-decreasing order for correct results, because the function uses a binary search internally.

If N is NULL, the function returns -1. Any NULL threshold values are skipped during comparison.

Syntax

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

Arguments

Arguments

Description

N

Required. The value to compare against the threshold list.

N1, N2, …

Required (at least one threshold). The sorted list of integer thresholds to compare against.

Examples

DROP DATABASE IF EXISTS test_interval;
CREATE DATABASE test_interval;
USE test_interval;

-- Basic comparison
SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200);
-- 23 < 30, so returns 3 (position of 17, the last threshold <= 23)

-- Equality advances to the next threshold
SELECT INTERVAL(10, 1, 10, 100);
-- 10 >= 10 (N2), so returns 2

-- Value below all thresholds
SELECT INTERVAL(0, 1, 10, 100);
-- 0 < 1, returns 0

-- Value above all thresholds
SELECT INTERVAL(101, 1, 10, 100);
-- 101 >= 100, returns 3

-- Duplicate thresholds
SELECT INTERVAL(10, 1, 10, 10, 10, 100);
-- 10 >= each 10, returns 4 (index of last 10 before 100)

-- NULL handling
SELECT INTERVAL(NULL, 1, 10, 100);
-- N is NULL, returns -1

SELECT INTERVAL(10, 1, NULL, 100);
-- NULL threshold is skipped, N=10 >= N1=1 and 10 < N3=100, returns 1

-- Negative values
SELECT INTERVAL(-10, -20, -10, 0, 10);
-- -10 >= -10 (-20 <= -10), returns 2

-- Floating-point values (converted to integer)
SELECT INTERVAL(2.9, 1, 2, 3, 4);
-- 2.9 -> 2, 2 >= 2, returns 2

-- String conversion to integer
SELECT INTERVAL('10', '1', '10', '100');
-- '10' -> 10, 10 >= 10, returns 2

-- Expressions as first argument
SELECT INTERVAL(5 + 5, 1, 10, 100);
-- 10 >= 10, returns 2

-- Table usage
DROP TABLE IF EXISTS t_func_interval;
CREATE TABLE t_func_interval(
    id INT,
    n INT,
    t1 INT,
    t2 INT,
    t3 INT
);
INSERT INTO t_func_interval VALUES
    (1, 0, 1, 10, 100),
    (2, 5, 1, 10, 100),
    (3, 10, 1, NULL, 100),
    (4, NULL, 1, 10, 100);
SELECT id, INTERVAL(n, t1, t2, t3) FROM t_func_interval ORDER BY id;
DROP TABLE t_func_interval;

DROP DATABASE test_interval;

Notes

  • The INTERVAL() function uses a binary search for efficiency, which requires the threshold list N1, N2, N3, ... to be sorted in non-decreasing order. If the thresholds are not sorted, the result is undefined.

  • This function is distinct from the INTERVAL keyword used in date arithmetic expressions such as DATE_ADD('2024-01-01', INTERVAL 1 DAY). Both can coexist in the same SQL statement.

  • Non-integer arguments (floating-point numbers, strings) are converted to integers before comparison. Strings that cannot be converted to an integer cause an error.

  • INTERVAL() requires at least one threshold argument. Calling it with only one argument (e.g., INTERVAL(N)) is an error.