MySQL Interval
Summary: in this tutorial, you will learn how to use MySQL interval values to perform date and time arithmetic.
Introduction to MySQL interval values
MySQL interval values are used mainly for date and time calculations. To create an interval value, you use the following expression:
INTERVAL expr unit
Code language: SQL (Structured Query Language) (sql)
Followed by the INTERVAL keyword is the expr that determines the interval value, and unit that specifies the interval unit. For example, to create 1-day interval, you use the following expression:
INTERVAL 1 DAY
Code language: SQL (Structured Query Language) (sql)
Notice that the INTERVAL and UNIT are case-insensitive therefore the following expression is equivalent to the one above:
interval 1 day
Code language: SQL (Structured Query Language) (sql)
We mainly use interval values for date and time arithmetic as shown below:
date + INTERVAL expr unit
date - INTERVAL expr unit
Code language: SQL (Structured Query Language) (sql)
The interval values are also used in various temporal functions such as DATE_ADD,DATE_SUB, TIMESTAMPADD and TIMESTAMPDIFF.
MySQL defines standard formats for expr and unit as illustrated in the following table:
| unit | expr |
|---|---|
| DAY | DAYS |
| DAY_HOUR | ‘DAYS HOURS’ |
| DAY_MICROSECOND | ‘DAYS HOURS:MINUTES:SECONDS.MICROSECONDS’ |
| DAY_MINUTE | ‘DAYS HOURS:MINUTES’ |
| DAY_SECOND | ‘DAYS HOURS:MINUTES:SECONDS’ |
| HOUR | HOURS |
| HOUR_MICROSECOND | ‘HOURS:MINUTES:SECONDS.MICROSECONDS’ |
| HOUR_MINUTE | ‘HOURS:MINUTES’ |
| HOUR_SECOND | ‘HOURS:MINUTES:SECONDS’ |
| MICROSECOND | MICROSECONDS |
| MINUTE | MINUTES |
| MINUTE_MICROSECOND | ‘MINUTES:SECONDS.MICROSECONDS’ |
| MINUTE_SECOND | ‘MINUTES:SECONDS’ |
| MONTH | MONTHS |
| QUARTER | QUARTERS |
| SECOND | SECONDS |
| SECOND_MICROSECOND | ‘SECONDS.MICROSECONDS’ |
| WEEK | WEEKS |
| YEAR | YEARS |
| YEAR_MONTH | ‘YEARS-MONTHS’ |
MySQL interval examples
The following statement adds 1 day to January 1st 2020 that returns January 2nd 2020:
SELECT '2020-01-01' + INTERVAL 1 DAY;
+-------------------------------+
| '2020-01-01' + INTERVAL 1 DAY |
+-------------------------------+
| 2020-01-02 |
+-------------------------------+
1 row in set (0.01 sec)
Code language: SQL (Structured Query Language) (sql)
If an interval value is used in an expression that involved a DATE or DATETIME value and the interval value is on the right side of the expression, you can use the negative value of the expr as shown in the following example:
SELECT '2020-01-01' + INTERVAL -1 DAY;
+--------------------------------+
| '2020-01-01' + INTERVAL -1 DAY |
+--------------------------------+
| 2019-12-31 |
+--------------------------------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
The following statement shows how to use DATE_ADD and DATE_SUB to add/subtract 1 month to/from a date value:
SELECT DATE_ADD('2020-01-01', INTERVAL 1 MONTH) 1_MONTH_LATER,
DATE_SUB('2020-01-01',INTERVAL 1 MONTH) 1_MONTH_BEFORE;
+---------------+----------------+
| 1_MONTH_LATER | 1_MONTH_BEFORE |
+---------------+----------------+
| 2020-02-01 | 2019-12-01 |
+---------------+----------------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
The following query uses TIMESTAMPADD(unit,interval,expression) function to add 30 minutes to a timestamp value:
SELECT TIMESTAMPADD(MINUTE,30,'2020-01-01') 30_MINUTES_LATER;
+---------------------+
| 30_MINUTES_LATER |
+---------------------+
| 2020-01-01 00:30:00 |
+---------------------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
MySQL interval practical example
Let’s create a new table called memberships for demonstration:
CREATE TABLE memberships (
id INT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(355) NOT NULL,
plan VARCHAR(255) NOT NULL,
expired_date DATE NOT NULL
);
Code language: SQL (Structured Query Language) (sql)
In the memberships table, the expired_date column stores the membership’s expire date of each member.
The following statement inserts some rows into the memberships table.
INSERT INTO memberships(email, plan, expired_date)
VALUES('john.doe@example.com','Gold','2017-07-13'),
('jane.smith@example.com','Platinum','2017-07-10'),
('david.corp@example.com','Silver','2017-07-15'),
('julia.william@example.com','Gold','2017-07-20'),
('peter.drucker@example.com','Silver','2017-07-08');
Code language: SQL (Structured Query Language) (sql)
Suppose today is 2017-07-06, you can find the members whose memberships are expired within 7 days using the following query:
SELECT
email,
plan,
expired_date,
DATEDIFF(expired_date, '2017-07-06') remaining_days
FROM
memberships
WHERE
'2017-07-06' BETWEEN DATE_SUB(expired_date, INTERVAL 7 DAY) AND expired_date;
Code language: SQL (Structured Query Language) (sql)
In this query, we used the DATE_SUB function to subtract the expired date by 7 days specified by an interval value (INTERVAL 7 DAY).
In this tutorial, you have learned how to use MySQL interval value for the date and time arithmetic.