mysql 常见的时间查询

最近使用的一些 时间查询统计

测试表结构如下：
Create Table |

CREATE TABLE test_t (
id int(10) NOT NULL AUTO_INCREMENT,
create_time datetime DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8

两个字段 方便操作
测试数据如下：

insert into test_t (id, create_time) values('1','2017-11-03 14:22:50');
insert into test_t (id, create_time) values('2','2017-11-04 14:22:54');
insert into test_t (id, create_time) values('3','2017-11-03 14:22:57');
insert into test_t (id, create_time) values('4','2017-11-01 14:23:00');
insert into test_t (id, create_time) values('5','2017-11-05 14:23:03');
insert into test_t (id, create_time) values('6','2017-11-01 14:23:11');
insert into test_t (id, create_time) values('7','2017-11-07 14:23:15');
insert into test_t (id, create_time) values('8','2017-11-02 14:23:27');
insert into test_t (id, create_time) values('9','2017-10-31 14:23:40');
insert into test_t (id, create_time) values('10','2017-11-06 14:38:37');
insert into test_t (id, create_time) values('11','2017-07-21 15:38:25');
insert into test_t (id, create_time) values('12','2017-08-18 15:38:35');
insert into test_t (id, create_time) values('13','2017-11-07 15:53:48');
insert into test_t (id, create_time) values('14','2016-11-07 16:20:25');

为了方便查看 ， 都叫上了 order by 排序

1.要统计每日的新增数据 ，就要把时间戳格式的时间 FORMAT 成年-月-日的格式，并按照 format 过的字段做分组（group by），计算 count

• SELECT DATE_FORMAT(create_time,'%Y-%m-%d') AS TIME , COUNT(*) AS COUNT FROM test_t GROUP BY TIME
  

2.要统计最近 7 天每天的新增数据，按时间倒序排序

• SELECT DATE_FORMAT(create_time,'%Y-%m-%d') AS TIME, COUNT(*) AS COUNT FROM test_t WHERE create_time >= DATE(NOW()) - INTERVAL 6 DAY GROUP BY DAY(create_time) ORDER BY create_time DESC;

3.要统计最近 7 天的新增数据，总计数据

• SELECT * FROM test_t WHERE DATE_SUB(CURDATE(), INTERVAL 6 DAY) <= DATE(create_time) ORDER BY create_time DESC
  

4.统计今日新增数据

• SELECT * FROM test_t WHERE TO_DAYS(create_time) = TO_DAYS(NOW());
  

5.统计昨日新增数据
方法 1：

• SELECT * FROM test_t WHERE TO_DAYS(NOW()) = 1 + TO_DAYS(create_time);
  

方法 2： 取出昨天的和今天的记录过滤掉今天的记录

6.统计最近一周的数据
note: 最近一周不等于最近 7 天，一周是从周日开始算的，比如测试的时间是 2017-11-7，那么往前的一周就是 11-5 开始

• SELECT * FROM test_t WHERE YEARWEEK(DATE_FORMAT(create_time,'%Y-%m-%d')) = YEARWEEK(NOW()) ORDER BY create_time DESC
  

7.统计最近一个月的数据
note: 最近一个月不等于最近 30 天

• SELECT * FROM test_t WHERE create_time BETWEEN DATE_SUB(NOW(),INTERVAL 1 MONTH) AND NOW() ORDER BY create_time DESC
  

8.统计上个月的数据

• SELECT * FROM test_t WHERE PERIOD_DIFF(DATE_FORMAT(NOW() , '%Y%m') , DATE_FORMAT(create_time, '%Y%m')) = 1 ORDER BY create_time DESC
  

9.统计本季度的数据

• SELECT * FROM test_t WHERE QUARTER(create_time)=QUARTER(NOW()) ORDER BY create_time DESC
  

10.统计上个季度的数据

• SELECT * FROM test_t WHERE QUARTER(create_time)=QUARTER(DATE_SUB(NOW(),INTERVAL 1 QUARTER)) ORDER BY create_time DESC
  

11.统计今年的数据

• SELECT * FROM test_t WHERE YEAR(create_time)=YEAR(NOW()) ORDER BY create_time DESC

12.统计去年的数据

• SELECT * FROM test_t WHERE YEAR(create_time)=YEAR(DATE_SUB(NOW(),INTERVAL 1 YEAR)) ORDER BY create_time DESC