Mysql之INTERVAL与DATE_SUB与EXTRACT函数的使用

1. INTERVAL

INTERVAL代表的是时间间隔
MySQL中的时间间隔类型有如下几种:

 

 

 

1.1 利用INTERVAL做时间的加减法

示例：

加法:SQL>SELECT DATE '2018-11-01' + INTERVAL '10 11' DAY_HOUR;
结果:2018-11-11 11:00:00

减法：SQL> select date '2018-11-11 11:00:00' -INTERVAL '10 11' DAY_HOUR ;
结果:2018-11-01 00:00:00

2. EXTRACT:用于返回日期/时间的单独部分，比如年、月、日、小时、分钟等等

　　格式:EXTRACT(unit FROM date)

　　unit取值表:

 

 

 示例1:

SELECT 
  EXTRACT(YEAR FROM NOW()) AS years ,
  EXTRACT(MONTH FROM NOW()) AS months,
  EXTRACT(DAY FROM NOW()) AS days;

　　结果:

 

示例2:

 SELECT NOW(),EXTRACT(DAY_SECOND FROM NOW());

结果:

 

 

 

3. DATE_SUB:从日期减去指定的时间间隔

DATE_SUB有个相反的函数DATE_ADD(),但因为业务上用DATE_SUB
的情况多,而且DATE_SUB也能实现增加时间间隔的功能,所以我大多用
DATE_SUB.

格式: DATE_SUB(日期, 时间间隔, 时间间隔类型type)

时间间隔即interval函数
间隔类型type可取:

 

 

 示例1:

SELECT DATE_SUB('2018-11-11 12:00',INTERVAL '0 12' DAY_HOUR);

结果:

 

 

 示例2:

SELECT DATE_SUB('2018-11-11 12:00',INTERVAL '1 2 3 4' DAY_SECOND);

结果:

 

 

 示例3:

SELECT DATE_SUB('2018-11-11 12:00',INTERVAL '1 2' YEAR_MONTH);

结果:

 

 

 

4 复合函数实现查询上个月月末月初日期

现在3个函数都了解了,怎么实现查询上个月的月初和月末的日期呢?

4.1 上个月月末

1.首先用 函数 查询出来这个月过了几天
select EXTRACT(DAY FROM NOW()) DAY

2.用 DATE_SUB减去这时间间隔

SELECT 
DATE_SUB(DATE_FORMAT(NOW(), '%y-%m-%d'),
INTERVAL EXTRACT(DAY FROM NOW()) DAY);

结果:

 

 

 

4.2 上个月月初

1.算出这个月过了几天

select EXTRACT(DAY FROM NOW()) DAY;

写此文的时间为11月7日,这个月过了7天,如果11月7日减去6天的话,就是月
初,所以

select EXTRACT(DAY FROM NOW())-1  DAY;

6天就是距离月初的时间间隔

2.用date_sub得到本月月初时间

SELECT
 DATE_SUB(DATE_FORMAT(NOW(), '%y-%m-%d'),
 INTERVAL EXTRACT(DAY FROM NOW()) - 1 DAY);

 

 3.继续用date_sub将上述结果,减去一个月

SELECT DATE_SUB(DATE_SUB(DATE_FORMAT(NOW(), '%y-%m-%d'),INTERVAL EXTRACT(DAY FROM NOW()) - 1 DAY),INTERVAL 1 MONTH);

结果: