Mysql 简明教程
MySQL - Interval Operator
MySQL INTERVAL Operator
MySQL 中的 INTERVAL 运算符用于创建两个不同事件或时间之间的间隔。该间隔可以是秒、分钟、小时、天等。因此，MySQL 主要使用此运算符来执行日期和时间计算，例如向日期和时间值添加或减去指定的时间间隔。
The INTERVAL operator in MySQL is used to create an interval between two different events or times. This interval can be in seconds, minutes, hours, days, etc. Thus, MySQL mainly uses this operator to perform date and time calculations, such as adding or subtracting a specified time interval from date and time values.
INTERVAL 运算符与各种日期和时间函数一起使用，并有助于在计算截止日期、计划事件等实时场景中使用。
INTERVAL operator is used with various date and time functions, and helps in real-time scenarios for calculating the deadlines, scheduling events, etc.
Syntax
以下是 MySQL 中 INTERVAL 运算符的语法 −
Following is the syntax of INTERVAL operator in MySQL −
INTERVAL expr unit其中，
Where,
-
expr: is a keyword that specifies the interval value.
-
unit: keyword determines the interval unit (such as DAY, HOUR, MINUTE, etc.).
Note: INTERVAL 和 UNIT 不区分大小写。
Note: The INTERVAL and UNIT are case-insensitive.
Standard Formats For Interval Expressions and Units
以下是 MySQL 针对间隔表达式及其相应单位的标准格式表 −
Following is the table of MySQL standard formats for the interval expressions and its corresponding unit −
Example
以下查询向日期“2023-04-14”添加 10 天 −
The following query adds 10 days to the date “2023-04-14” −
SELECT '2023-04-14' + INTERVAL 10 DAY;Example
以下查询从日期“2023-04-14”减去 5 天 −
The following query subtracts 5 days from the date "2023-04-14" −
SELECT '2023-04-14' - INTERVAL 5 DAY;Example
此处，我们向日期时间值“2023-04-14 09:45:30.000”添加两小时 −
Here, we are adding two hours to the datetime value "2023-04-14 09:45:30.000" −
SELECT '2023-04-14 09:45:30.000' + INTERVAL 2 HOUR;Example
以下查询从日期时间值“2023-04-14 09:45:30.000”减去 60 分钟 −
The following query is subtracting sixty minutes from the datetime value "2023-04-14 09:45:30.000" −
SELECT '2023-04-14 09:45:30.000' - INTERVAL 60 MINUTE;Example
此处，我们向日期“2023-04-14”添加和删除一个 −
Here, we are adding and deleting one from the date '2023-04-14' −
SELECT DATE_ADD('2023-04-14', INTERVAL 1 MONTH) ADD_ONE_MONTH,
DATE_SUB('2023-04-14',INTERVAL 1 MONTH) SUB_ONE_MONTH;Example
在以下查询中，我们使用 TIMESTAMPADD() 函数向时间戳值添加两小时 −
In the following query, we are using the TIMESTAMPADD() function to add two hours to the timestamp value −
SELECT TIMESTAMPADD (HOUR, 2, '2020-01-01 03:30:43.000') 2_HOURS_LATER;Example
现在，让我们使用以下查询创建一个名为 OTT 的表 −
Now, let us create a table with a name OTT using the following query −
CREATE TABLE OTT (
ID INT NOT NULL,
SUBSCRIBER_NAME VARCHAR (200) NOT NULL,
MEMBERSHIP VARCHAR (200),
EXPIRED_DATE DATE NOT NULL
);使用以下查询，我们使用 INSERT INTO 语句插入一些记录到上述创建的表中，如下所示 −
Using the following query, we are inserting some records into the above-created table using the INSERT INTO statement as shown below −
INSERT INTO OTT VALUES
(1, 'Dhruv', 'Silver', '2023-04-30'),
(2, 'Arjun','Platinum', '2023-04-01'),
(3, 'Dev','Silver', '2023-04-23'),
(4, 'Riya','Gold', '2023-04-05'),
(5, 'Aarohi','Platinum', '2023-04-02'),
(6, 'Lisa','Platinum', '2023-04-25'),
(7, 'Roy','Gold', '2023-04-26');表创建如下 −
The table is created as −
现在，我们从 OTT 表中选择订阅者的数据，他们的会员资格将在特定的“2023-04-01”日期后 7 天内到期。
Now, we are selecting data from the OTT table for the subscribers whose membership is about to expire within the next 7 days from the specific date of '2023-04-01'.
SELECT ID, SUBSCRIBER_NAME, MEMBERSHIP, EXPIRED_DATE,
DATEDIFF(expired_date, '2023-04-01') EXPIRING_IN
FROM OTT
WHERE '2023-04-01' BETWEEN DATE_SUB(EXPIRED_DATE, INTERVAL 7 DAY)
AND EXPIRED_DATE;执行给定的查询后，输出如下：
On executing the given query, the output is displayed as follows −
Interval Operator Using Client Program
除了使用 SQL 查询在 MySQL 表中执行 Interval 运算符之外，我们还可以使用客户端程序在表上应用 INTERVAL 运算符。
In addition to executing the Interval Operator in MySQL table using an SQL query, we can also apply the INTERVAL operator on a table using a client program.