# 间隔

在本教程中，您将学习如何使用MySQL间隔值来执行日期和时间算术运算。

## MySQL间隔值简介

MySQL间隔值主要用于日期和时间计算。要创建间隔值，请使用以下表达式：

```sql
INTERVAL expr unit
```

其次是`INTERVAL`关键字是确定间隔值的`expr`，以及指定间隔单位的单位。 例如，要创建`1`天间隔，请使用以下表达式：

```sql
INTERVAL 1 DAY
```

请注意，`INTERVAL`和`UNIT`不区分大小写，因此以下表达式与上述表达式相当：

```sql
interval 1 day
```

主要使用日期和时间算术的间隔值，如下所示：

```sql
date + INTERVAL expr unit
date - INTERVAL expr unit
```

间隔值也用于各种时间函数，如[DATE\_ADD](http://www.yiibai.com/mysql/date_add.html)，[DATE\_SUB](http://www.yiibai.com/mysql/date_sub.html)，`TIMESTAMPADD`和`TIMESTAMPDIFF`。

MySQL定义了`expr`和`unit`的标准格式，如下表所示：

| 单位(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间隔示例

以下语句在`2020-01-01`日期上增加1天返回结果为：`2020-01-02`：

```sql
mysql>  SELECT '2020-01-01' + INTERVAL 1 DAY;
+-------------------------------+
| '2020-01-01' + INTERVAL 1 DAY |
+-------------------------------+
| 2020-01-02                    |
+-------------------------------+
1 row in set (0.01 sec)
```

如果在涉及[DATE](http://www.yiibai.com/mysql/date.html)或[DATETIME](http://www.yiibai.com/mysql/datetime.html)值的表达式中使用了间隔值，并且间隔值位于表达式的右侧，则可以使用`expr`的负值，如以下示例所示：

```sql
mysql> SELECT '2020-01-01' + INTERVAL -1 DAY;
+--------------------------------+
| '2020-01-01' + INTERVAL -1 DAY |
+--------------------------------+
| 2019-12-31                     |
+--------------------------------+
1 row in set
```

以下语句显示如何使用[DATE\_ADD](http://www.yiibai.com/mysql/date_add.html)和[DATE\_SUB](http://www.yiibai.com/mysql/date_sub.html)从日期值添加/减去`1`个月：

```sql
mysql> 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
```

以下查询使用`TIMESTAMPADD(unit，interval，expression)`函数向时间戳值添加`30`分钟：

```sql
mysql> SELECT TIMESTAMPADD(MINUTE,30,'2020-01-01') 30_MINUTES_LATER;
+---------------------+
| 30_MINUTES_LATER    |
+---------------------+
| 2020-01-01 00:30:00 |
+---------------------+
1 row in set
```

## MySQL间隔实例

我们创建一个名为`memberships`的新表，用于演示：

```sql
USE testdb;
CREATE TABLE memberships (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(355) NOT NULL,
    plan VARCHAR(255) NOT NULL,
    expired_date DATE NOT NULL
);
```

在`memberships`表中，`expired_date`列存储每个会员的会员资格到期日。

以下语句将一些行[插入](http://www.yiibai.com/mysql/insert-statement.html)到`memberships`表中。

```sql
INSERT INTO memberships(email, plan, expired_date)
VALUES('john.doe@yiibai.com','Gold','2018-07-13'),
      ('jane.minsu@yiibai.com','Platinum','2018-07-10'),
      ('david.corp@yiibai.com','Silver','2018-07-15'),
      ('julia.william@yiibai.com','Gold','2018-07-20'),
      ('peter.drucker@yiibai.com','Silver','2018-07-08');
```

假设今天是`2018-07-06`，您可以使用以下语句查询在`7`天内会员资格已过期的会员：

```sql
SELECT 
    email,
    plan,
    expired_date,
    DATEDIFF(expired_date, '2018-07-06') remaining_days
FROM
    memberships
WHERE
    '2018-07-06' BETWEEN DATE_SUB(expired_date, INTERVAL 7 DAY) AND expired_date;
```

执行上面查询语句后，得到以下结果 -

```sql
+--------------------------+----------+--------------+----------------+
| email                    | plan     | expired_date | remaining_days |
+--------------------------+----------+--------------+----------------+
| john.doe@yiibai.com      | Gold     | 2018-07-13   |              7 |
| jane.minsu@yiibai.com    | Platinum | 2018-07-10   |              4 |
| peter.drucker@yiibai.com | Silver   | 2018-07-08   |              2 |
+--------------------------+----------+--------------+----------------+
3 rows in set
```

在此查询中，我们使用`DATE_SUB`函数将间隔值(`INTERVAL 7 DAY`)指定的过期日期减去`7`天。

在本教程中，您已经学习了如何使用MySQL间隔值进行日期和时间算术。


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://hezhiqiang-book.gitbook.io/mysql/di-er-zhang/jian-ge.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.