间隔

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

MySQL间隔值简介

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

INTERVAL expr unit

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

INTERVAL 1 DAY

请注意,INTERVALUNIT不区分大小写,因此以下表达式与上述表达式相当:

interval 1 day

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

date + INTERVAL expr unit
date - INTERVAL expr unit

间隔值也用于各种时间函数,如DATE_ADDDATE_SUBTIMESTAMPADDTIMESTAMPDIFF

MySQL定义了exprunit的标准格式,如下表所示:

单位(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

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

如果在涉及DATEDATETIME值的表达式中使用了间隔值,并且间隔值位于表达式的右侧,则可以使用expr的负值,如以下示例所示:

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

以下语句显示如何使用DATE_ADDDATE_SUB从日期值添加/减去1个月:

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分钟:

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的新表,用于演示:

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列存储每个会员的会员资格到期日。

以下语句将一些行插入memberships表中。

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天内会员资格已过期的会员:

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;

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

+--------------------------+----------+--------------+----------------+
| 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间隔值进行日期和时间算术。

Last updated