MySQL Interval

 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 nulldate and time calculations. To create an interval value, you use the following expression:

INTERVAL expr unit

Followed by the INTERVAL keyword is the expr that determines the interval value, and unit that specifies the interval unit. For example, to create a 1-day interval, you use the following expression:

INTERVAL 1 DAY

Notice that the INTERVAL and UNIT are case-insensitive therefore the following expression is equivalent to the one above:

interval 1 day

We mainly use interval values for date and time arithmetic as shown below:

date + INTERVAL expr unit
date - INTERVAL expr unit

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)

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) 

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)

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)

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
);

In the memberships table, the expired_date column stores the membership’s expiry 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');

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;

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 the MySQL interval value for date and time arithmetic.