{"id":6049,"date":"2017-07-06T02:07:53","date_gmt":"2017-07-06T09:07:53","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=6049"},"modified":"2023-12-31T02:58:53","modified_gmt":"2023-12-31T09:58:53","slug":"mysql-interval","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-interval\/","title":{"rendered":"MySQL Interval"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use MySQL interval values to perform date and time arithmetic.<\/p>\n\n\n\n

Introduction to MySQL interval values<\/h2>\n\n\n\n

MySQL interval values are used mainly for date <\/a>and time<\/a> calculations. To create an interval value, you use the following expression:<\/p>\n\n\n

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