欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2023-09-27 15:33:25
本文实例讲述了mysql日期和时间的间隔计算。分享给大家供大家参考,具体如下: 我们首先得明确一点,mysql间隔值主要用于日期和时间计算,如果我们要要创建间隔值,可以使用以下表达式...

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

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

interval expr unit

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

interval 1 day

不过我们要注意,interval和unit不区分大小写,因此以下表达式与上述表达式相当:

interval 1 day

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

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:

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的负值,如以下示例所示:

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个月:

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

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

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,然后我们来使用sql语句查询在未来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

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

那咱们今天就到这里了。