摘要:在本教程中,您将学习如何使用 MySQL 间隔值来执行日期和时间算术。
MySQL 间隔值简介
MySQL 间隔值主要用于日期和时间计算。要创建间隔值,请使用以下表达式:
INTERVAL expr unit
Code language: SQL (Structured Query Language) (sql)
INTERVAL
关键字后面是确定间隔值的expr
和指定间隔单位的unit
。例如,要创建 1 天的间隔,请使用以下表达式:
INTERVAL 1 DAY
Code language: SQL (Structured Query Language) (sql)
请注意, INTERVAL
和UNIT
不区分大小写,因此以下表达式与上面的表达式等效:
interval 1 day
Code language: SQL (Structured Query Language) (sql)
我们主要使用间隔值进行日期和时间运算,如下所示:
date + INTERVAL expr unit
date - INTERVAL expr unit
Code language: SQL (Structured Query Language) (sql)
间隔值还用于各种时间函数,例如DATE_ADD,
DATE_SUB
、 TIMESTAMPADD
和TIMESTAMPDIFF
。
MySQL 定义了expr
和unit
的标准格式,如下表所示:
单元 | 表达式 |
---|---|
天 | 天 |
DAY_HOUR | “天数小时” |
DAY_MICROSECOND | '天小时:分钟:秒.微秒' |
DAY_MINUTE | “天数小时:分钟” |
DAY_SECOND | “天数小时:分钟:秒” |
小时 | 小时 |
小时_微秒 | '小时:分钟:秒.微秒' |
小时_分钟 | “小时:分钟” |
HOUR_SECOND | “小时:分钟:秒” |
微秒 | 微秒 |
分钟 | 分钟 |
分钟_微秒 | '分钟:秒.微秒' |
分钟_秒 | “分钟:秒” |
月 | 月数 |
四分之一 | 季度 |
第二 | 秒 |
SECOND_微秒 | '秒.微秒' |
星期 | 几周 |
年 | 年 |
年_月 | “年月” |
MySQL 间隔示例
以下语句将January 1st 2020
添加 1 天,返回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)
Code language: SQL (Structured Query Language) (sql)
如果在涉及DATE
或DATETIME
值的表达式中使用间隔值,并且间隔值位于表达式的右侧,则可以使用expr
的负值,如下例所示:
SELECT '2020-01-01' + INTERVAL -1 DAY;
+--------------------------------+
| '2020-01-01' + INTERVAL -1 DAY |
+--------------------------------+
| 2019-12-31 |
+--------------------------------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
以下语句显示如何使用DATE_ADD
和DATE_SUB
在日期值中添加/减去 1 个月:
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)
Code language: SQL (Structured Query Language) (sql)
以下查询使用TIMESTAMPADD(unit,interval,expression)
函数将 30 分钟添加到时间戳值:
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)
Code language: SQL (Structured Query Language) (sql)
MySQL间隔实际例子
让我们创建一个名为memberships
的新表来进行演示:
CREATE TABLE memberships (
id INT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(355) NOT NULL,
plan VARCHAR(255) NOT NULL,
expired_date DATE NOT NULL
);
Code language: SQL (Structured Query Language) (sql)
在memberships
表中, expired_date
列存储每个会员的会员资格到期日期。
以下语句将一些行插入到memberships
表中。
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');
Code language: SQL (Structured Query Language) (sql)
假设今天是2017-07-06
,您可以使用以下查询找到会员资格在 7 天内过期的会员:
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;
Code language: SQL (Structured Query Language) (sql)
在此查询中,我们使用DATE_SUB
函数将过期日期减去间隔值 ( INTERVAL 7 DAY
) 指定的 7 天。
在本教程中,您学习了如何使用 MySQL 间隔值进行日期和时间算术。