mysql日期和时间的间隔计算实例分析

本文实例讲述了mysql日期和时间的间隔计算。分享给大家供大家参考，具体如下：

我们首先得明确一点，mysql间隔值主要用于日期和时间计算，如果我们要要创建间隔值，可以使用以下表达式：

?

1	INTERVAL expr unit

然后嘞，我们得了解，INTERVAL关键字是确定间隔值的expr，以及指定间隔单位的单位。 例如，要创建1天间隔，我们就要使用以下表达式：

?

1	INTERVAL 1 DAY

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

?

1	interval 1 day

日期和时间算术的间隔值的实际用法和语法，我们可以看下面的代码：

?

1 2	date + INTERVAL expr unit date - INTERVAL expr unit

间隔值同样也用于各种时间函数，如DATE_ADD，DATE_SUB，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'

 

我们来看个实例，下面的sql语句在2020-01-01日期上增加1天返回结果为，2020-01-02：

?

1 2 3 4 5 6 7

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或DATETIME值的表达式中使用了间隔值，并且间隔值位于表达式的右侧，则可以使用expr的负值，如以下示例所示：

?

1 2 3 4 5 6 7

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

我们再来看如何使用DATE_ADD和DATE_SUB从日期值添加/减去1个月：

?

1 2 3 4 5 6 7 8

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

完事再来看一个sql使用TIMESTAMPADD(unit，interval，expression)函数向时间戳值添加30分钟：

?

1 2 3 4 5 6 7

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

说这么多，大家可能还有点懵，不怕，来看具体的操作，我们创建一个名为memberships的新表，用于演示：

?

1 2 3 4 5 6

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表中：

?

1 2 3 4 5 6

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，然后我们来使用sql语句查询在未来7天内会员资格将要过期的会员：

?

1 2 3 4 5 6 7 8 9

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;

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

?

1 2 3 4 5 6 7 8

+ --------------------------+----------+--------------+----------------+ | 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

在上面的sql中，DATE_SUB函数将间隔值(INTERVAL 7 DAY)指定的过期日期减去7天，也就是说，本来某条数据的日期是13号，减去七天，那就是6号了。大概就是这个意思了，相信大家都有一定的理解了。

那咱们今天就到这里了。

希望本文所述对大家MySQL数据库计有所帮助。

原文链接：https://blog.csdn.net/luyaran/article/details/80942328