MySQL INTERVAL
MySQL interval is an operator, which is based on the binary search algorithm to search the items and returns the value from 0 to N. It is mainly used to calculate the date and time values.
We can use the following syntax to create an interval value:
In the above syntax, the expr is used to determine the interval value, and the unit represents the interval unit. For example, if we want to create a two-day interval, we can use the expression as follows:
It is to note that the INTERVAL and UNIT are not case sensitive. Therefore, the below expression is equivalent to the previous expression:
We can use the interval values for date and time as the below statement:
The interval values can also be used in various temporal functions such as DATE_SUB, DATE_ADD, TIMESTAMPDIFF, TIMESTAMPADD, etc.
MySQL describes the standard formats for unit associated with the expression are illustrated in the below 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:SECCONDS’ | |
| HOUR | HOURS | |
| HOUR_MICROSECOND | ‘HOURS:MINUTES:SECONDS.MICROSECONDS’ | |
| HOUR_SECOND | ‘HOURS:MINUTES:SECONDS’ | |
| HOUR_MINUTE | ‘HOURS:MINUTES’ | |
| 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
Let us understand how we can works with interval expression in MySQL through various examples. The following query adds five days to February 1st, 2020 that returns February 6th, 2020:
It will return the below output:
If we specify the value of DATE or DATETIME as an interval value on the right side of the expression, the negative value of an expr can be used. See the below example:
We will get the output as follows:
Next, we will see the use of DATE_SUB and DATE_ADD function to add or subtract date/month/time from a date value. See the below statements:
It will return the below output:
Now, we will use the TIMESTAMPADD (unit, interval, expression) function in the below query that will add 45 minutes to a timestamp value:
We will see the below output:
Let us see the use of interval in the MySQL table. First, we will create a new table named service_memberships using the below statement:
Next, we will fill records into the table using the INSERT statement as follows:
Next, execute the SELECT statement to verify the records:
Suppose we want to find the members whose memberships are expired within 7 days from the date 2020-06-05. We can find these details by using the following query:
In this statement, we have used the DATE_SUB function that subtracts the remaining days by seven days specified by an interval value. After executing the statement, we will get the below output:
