MySQL Interval – Detailed Guide

In this tutorial, we will learn about the MySQL INTERVAL keyword and its values to perform operations on the date and time arithmetic. So, let’s get started!

Also read: While Loop in MySQL – A Complete Reference

Introduction to MySQL INTERVAL

In MySQL, the INTERVAL keyword is used to add or subtract date and time values. For example, if you want to add five days to the current day and display the new date, you can use the INTERVAL keyword.

There are a bunch of interval values that MySQL provides us. We will see a complete list of these interval values down below.

But first, let’s see how can we use the interval keyword.

INTERVAL exp UNIT;Code language: SQL (Structured Query Language) (sql)

Here, the exp denotes the expression such as 1, 2, 100, 200, or anything else depending on the unit. The unit is the measurement to count the date and time such as day, hour, minute, seconds, etc.

In the above syntax, the INTERVAL keyword and the unit name, both are case-insensitive.

We can add or subtract the interval values as shown below-

date + INTERVAL exp UNIT
date - INTERVAL exp UNIT
Code language: SQL (Structured Query Language) (sql)

Note that, the date you are specifying must be in the valid date-time format.

The interval values can also be used with other date-time functions such as ADDDATE(), DATE_ADD(), DATE_SUB(), TIMESTAMPDIFF() etc.

Formats of Expression and Units

The following table shows the standard formats of the exp and the UNITs-

Examples of MySQL INTERVAL

In the first example, we will add 1 day to the current date. The current date is 2022-07-05 (YYYY-MM-DD), so the output must be 2022-07-06.

SELECT CURDATE() + INTERVAL 1 DAY;Code language: SQL (Structured Query Language) (sql)

You can also subtract the interval from the date. Moreover, you can specify your own date-time as well, but that must be in the valid date-time format. Check the example below-

SELECT '2022-01-01' - INTERVAL 1 DAY;
Code language: SQL (Structured Query Language) (sql)

Here, we have specified the first day of the year and tried to subtract 1 day from it. The output should contain the previous year. Let’s see the output.

As you can see, we got an expected output.

Let’s see how can we use the interval values with other date-time functions.

SELECT DATE_ADD('2022-01-01',INTERVAL 10 DAY);
SELECT DATE_SUB('2022-01-01',INTERVAL 10 DAY);Code language: SQL (Structured Query Language) (sql)

Here we have used the DATE_ADD() and DATE_SUB() functions to add and subtract the interval values from the specified date.

Let’s see the output.

As you can see here, we have got the correct output.

Practical Examples of MySQL INTERVAL

Now we will see some practical examples to use the interval values.

We will create a table in which we will store the members’ data along with their date of registration and the date of expiration of the membership.

We will calculate the date of expiration using the DATE_ADD() function.

CREATE TABLE members(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
reg_date DATE DEFAULT(CURDATE()),
exp_date DATE DEFAULT(DATE_ADD(reg_date,INTERVAL 1 YEAR))
);
Code language: SQL (Structured Query Language) (sql)

Here, at the time of the registration, MySQL will set the reg_date column value to the current date. Depending on the registration date, the expiry date will be calculated.

Let’s try inserting values and checking if the expiry date column gets the correct values or not. For that, we will explicitly insert some of the registration dates.

INSERT INTO members(name) VALUES ("Bob"),("Vilas"),("John");
INSERT INTO members(reg_date,name) VALUES ('2022-08-01',"Adam"),('2022-08-04',"Cetty");Code language: SQL (Structured Query Language) (sql)

Let’s check the output.

SELECT * FROM members;Code language: SQL (Structured Query Language) (sql)

As you can see, the expiry dates are set to 1 year after the registration dates.

Summary

In this tutorial, we have learned about the interval keyword and its values. Note that, the scope of the interval is very wide. You can use it for different purposes such as finding the students who are going to complete the course in the next 10 days, the list of members whose membership is going to be expired next month, etc. Practice !!