MySQL INTERVAL() Function

MySQL INTERVAL() Function

The MySQL INTERVAL() function returns the index of the argument that is less than or equal to the first argument. This function is essential for performing interval-based calculations in SQL queries.

Syntax

SELECT INTERVAL(N, N1, N2, N3, ...) AS result
FROM table_name;

The INTERVAL function has the following components:

  • N: The number to be compared.
  • N1, N2, N3, ...: The list of numbers to compare with N.
  • result: An alias for the resulting index.
  • table_name: The name of the table from which to retrieve the data.

Example MySQL INTERVAL Function

Let's look at some examples of the MySQL INTERVAL function:

Step 1: Using the Database

USE mydatabase;

This query sets the context to the database named mydatabase.

MySQL USE DATABASE

Step 2: Creating a Table

Create a table to work with:

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    age INT
);

This query creates a table named employees with columns for id, first_name, last_name, and age.

MySQL CREATE TABLE

Step 3: Inserting Initial Rows

Insert some initial rows into the table:

INSERT INTO employees (first_name, last_name, age)
VALUES ('John', 'Doe', 25),
       ('Jane', 'Smith', 30),
       ('Jim', 'Brown', 45),
       ('Jake', 'White', 35),
       ('Jill', 'Green', 50);

This query inserts five rows into the employees table.

MySQL INSERT INTO TABLE

Step 4: Using INTERVAL with WHERE Clause

Use the INTERVAL function to find the interval index of a column value:

SELECT first_name, last_name, INTERVAL(age, 20, 30, 40, 50) AS age_interval
FROM employees;

This query retrieves the first_name and last_name columns from the employees table and returns the interval index of the age column based on the specified intervals.

MySQL INTERVAL WITH WHERE CLAUSE

Step 5: Using INTERVAL with Multiple Columns

Use the INTERVAL function with multiple columns:

SELECT first_name, last_name, INTERVAL(age, 25, 35, 45) AS age_group
FROM employees;

This query retrieves the first_name and last_name columns from the employees table and returns the age group based on the specified intervals.

MySQL INTERVAL WITH MULTIPLE COLUMNS

Step 6: Using INTERVAL with Constants

Use the INTERVAL function with constants:

SELECT first_name, last_name, INTERVAL(age, 18, 25, 35, 45, 60) AS age_bracket
FROM employees;

This query retrieves the first_name and last_name columns from the employees table and returns the age bracket based on the specified intervals.

MySQL INTERVAL WITH CONSTANTS

Conclusion

The MySQL INTERVAL function is a powerful tool for performing interval-based calculations in SQL queries. Understanding how to use the INTERVAL function is essential for effective data querying and analysis in MySQL.