***本文以 users 表为例, create_time作为日期字段名筛选条件
DATE_FORMAT(日期参数, %Y-%m-%d %H:%i:%s)或 DATE_FORMAT(日期参数, %Y-%c-%d %h:%i:%s)
( %m 和 %c 都可以表示月份;%H 与 %h 分别代表24小时和12小时制 )
比如格式化日期:2021-11-10 19:23:50%Y-%m-%d %H:%i:%s ( 年-月-日 时:分:秒)
输出结果:2021-11-10 19:23:50
%Y-%c-%d %h:%i (年-月-日 时:分)
输出结果:2021-11-10 05:23
%Y-%m-%d (年-月-日)
输出结果:2021-11-10
1.查询本月数据
SELECT * FROM users WHERE DATE_FORMAT(create_time, '%Y%m') = DATE_FORMAT(DATE_SUB(curdate(), INTERVAL 0 MONTH),'%Y%m')
或
SELECT * FROM users WHERE DATE_FORMAT(create_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )
2.查看上个月数据(查询前 n 月数据,即修改‘DATE_SUB(curdate(), INTERVAL 1 MONTH),’%Y %m’)’中的 数值,将 1 改为 n)
SELECT * FROM users WHERE DATE_FORMAT(create_time, '%Y%m') = DATE_FORMAT(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y%m')
或
SELECT * FROM users WHERE PERIOD_DIFF(DATE_FORMAT( now() , '%Y%m') , DATE_FORMAT(create_time, '%Y%m' )) =1
3.查询指定天数数据
### 今天
SELECT * FROM users WHERE to_days(create_time) = to_days(now());
### 昨天
SELECT * FROM users WHERE TO_DAYS( NOW( ) ) - TO_DAYS(create_time) <= 1
### 近n天(查询前 n 天数据,即修改‘DATE_SUB(curdate(), INTERVAL 7 DAY ),’%Y %m’)’中的 数值,将 7 改为 n)
SELECT * FROM users WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(create_time)
4.查询季度数据
### 查询所有该季度数据
select * FROM users where QUARTER(create_date)=QUARTER(now());
### 查询今年该季度数据
select * FROM users where QUARTER(create_time)=QUARTER(now()) and YEAR(now()) = YEAR(create_time);
### 查询指定年 季度数据(这里是以 查询2年前该季度数据为例)
select * FROM users where QUARTER(create_time)=QUARTER(now()) and DATE_FORMAT(create_time, '%Y') = DATE_FORMAT(DATE_SUB(curdate(), INTERVAL 2 YEAR),'%Y');
5.查询年数据
### 查询本年数据
select * FROM users where YEAR(now()) = YEAR(create_time);
### 查询上年数据
select * FROM users where year(create_time)=year(date_sub(now(),interval 1 year));
或
select * FROM users where DATE_FORMAT(create_time, '%Y') = DATE_FORMAT(DATE_SUB(curdate(), INTERVAL 1 YEAR),'%Y');
6.查询周数据
### 查询本周数据
SELECT * FROM users WHERE YEARWEEK(DATE_FORMAT(create_time,'%Y-%m-%d')) = YEARWEEK(now());
### 查询上周数据
SELECT * FROM users WHERE YEARWEEK(DATE_FORMAT(create_time,'%Y-%m-%d')) = YEARWEEK(now()) -1;
### 查询3周前数据
SELECT * FROM users WHERE YEARWEEK(DATE_FORMAT(create_time,'%Y-%m-%d')) = YEARWEEK(now()) -3;