mysql查询某时间段的数据

1、查询今天、昨天、近7天、近30天数据

• （1）查询今天数据

-- 方式 1
SELECT * FROM guojia_zwfw_up_pro_accept a WHERE TO_DAYS(a.ApplyDate) = TO_DAYS(NOW());
-- TO_DAYS(字段) 函数，计算时间0开始计算到data时间点的天数（即从公元元年的第一天开始）
-- NOW()：显示当前系统时间，格式为yyyy-MM-dd HH:mm:ss

-- 方式2
SELECT * FROM guojia_zwfw_up_pro_accept a WHERE DATE(a.ApplyDate) = CURDATE(); 
-- DATE(字段) 函数，按天格式化时间，格式为：yyyy-MM-dd
-- CURDATE()：显示当前系统时间，格式为yyyy-MM-dd
1
2
3
4
5
6
7
8
9

• （2）查询昨天数据

-- 方式 1
SELECT * FROM guojia_zwfw_up_pro_accept a WHERE TO_DAYS(NOW()) - TO_DAYS(a.ApplyDate) = 1;

-- 方式2
SELECT * FROM guojia_zwfw_up_pro_accept a WHERE DATE(a.ApplyDate) = DATE_SUB(CURDATE(), INTERVAL 1 DAY);
-- DATE_SUB(date,INTERVAL expr unit)函数，计算某个时间点之前的时间，date：指定的时间，expr间隔周期，unit周期单位（day、month、year）
1
2
3
4
5
6

• （3）查询近7天数据

SELECT * FROM guojia_zwfw_up_pro_accept a where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= DATE(a.ApplyDate);
1

• （4）查询近30天数据

SELECT * FROM guojia_zwfw_up_pro_accept a WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= DATE(a.ApplyDate);
1

2、查询本周、上周数据

• （1）查询本周数据

SELECT * FROM guojia_zwfw_up_pro_accept a WHERE YEARWEEK(DATE_FORMAT(a.ApplyDate,'%Y-%m-%d')) = YEARWEEK(NOW());
-- YEARWEEK(NOW())函数，显示当前时间是本年第多少周
-- DATE_FORMAT(date,format)函数，按照指定样式格式化时间
1
2
3

• （2）查询上周数据

SELECT * FROM guojia_zwfw_up_pro_accept a WHERE YEARWEEK(DATE_FORMAT(a.ApplyDate,'%Y-%m-%d')) = YEARWEEK(NOW()) - 1;
1

3、查询本月、上个月、距离当前现在6个月的数据

• （1）查询本月数据

SELECT * FROM guojia_zwfw_up_pro_accept a WHERE DATE_FORMAT(a.ApplyDate, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m');
1

• （2）查询上个月数据

-- 方式 1
SELECT * FROM guojia_zwfw_up_pro_accept a WHERE PERIOD_DIFF(DATE_FORMAT(now(), '%Y%m') , DATE_FORMAT(a.ApplyDate, '%Y%m')) = 1;
-- PERIOD_DIFF(P1,P2)函数返回两个日期之间的月份数

-- 方式 2
SELECT * FROM guojia_zwfw_up_pro_accept a WHERE DATE_FORMAT(a.ApplyDate,'%Y-%m') = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m');

-- 方式 3
SELECT * FROM guojia_zwfw_up_pro_accept a WHERE YEAR(a.ApplyDate) = YEAR(NOW()) AND MONTH(a.ApplyDate) = MONTH(now()) - 1;
-- YEAR(date)计算年份
-- MONTH(date)计算月份

-- 方式 4 需要额外传参
SELECT * FROM guojia_zwfw_up_pro_accept a WHERE a.ApplyDate BETWEEN '上月第一天' AND '本月第一天';
-- 优化向
SELECT * FROM guojia_zwfw_up_pro_accept a WHERE a.ApplyDate BETWEEN (SELECT date_add(curdate() - DAY(curdate()) + 1, INTERVAL -1 MONTH))  AND (SELECT date_add(curdate() - DAY(curdate()) + 1, INTERVAL 0 MONTH));
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

• （3）距离当前现在6个月的数据

SELECT * FROM sythqzk.guojia_zwfw_up_pro_accept WHERE ApplyDate BETWEEN DATE_SUB(NOW(),INTERVAL 6 MONTH) AND NOW();
-- 统计数据不含当月数据
1
2

4、查询本季度、上个季度数据

• （1）查询本季度数据

SELECT * FROM guojia_zwfw_up_pro_accept WHERE YEAR(ApplyDate) = YEAR(NOW()) AND QUARTER(ApplyDate) = QUARTER(NOW());
-- QUARTER(date) 查询当前时间所属年份的季度
1
2

• （2）查询上个季度数据

-- 方式 1
SELECT * FROM guojia_zwfw_up_pro_accept WHERE YEAR(ApplyDate) = YEAR(NOW()) AND QUARTER(ApplyDate) = QUARTER(NOW()) - 1;

-- 方式 2
SELECT * FROM guojia_zwfw_up_pro_accept WHERE YEAR(ApplyDate) = YEAR(NOW()) AND QUARTER(ApplyDate) = QUARTER(DATE_SUB(NOW(),INTERVAL 1 QUARTER));
1
2
3
4
5

5、查询今年、昨年数据

• （1）查询今年数据

SELECT * FROM guojia_zwfw_up_pro_accept WHERE YEAR(ApplyDate)=YEAR(NOW());
1

• （2）查询昨年数据

-- 方式 1
SELECT * FROM guojia_zwfw_up_pro_accept WHERE YEAR(NOW()) - YEAR(ApplyDate) = 1;

-- 方式 2
SELECT * FROM guojia_zwfw_up_pro_accept WHERE YEAR(ApplyDate)=YEAR(DATE_SUB(NOW(),INTERVAL 1 YEAR));
1
2
3
4
5

6、查询小于某个时间点数据（mysql是字符串时间）

巧用 字符串转时间戳函数 （STR_TO_DATE(str,format)）

-- 这里巧用 字符串转时间戳函数 （STR_TO_DATE(str,format)）
SELECT STR_TO_DATE('2021-06-16 14:55:43','%Y-%m-%d %H:%i:%s') < '2021-06-15 14:55:43' AS count;

-- 真实场景 获取小于 xx 时间点数据
SELECT pb.apply_id, pb.service_no, pb.service_name, pb.service_type, pb.info_type, pb.dept_id, pb.dept_name, 
	pb.agent_type, pb.apply_nature, pb.user_name, pb.id_card, pb.mobile, pb.apply_time, psa.handler_account, 
	psa.handler_name
FROM pretrial_signfor_assign psa, pretrial_base pb, pretrial_process_direction ppd 
WHERE psa.pretrial_id = pb.apply_id AND psa.is_sign = 1 AND psa.is_allocated = 1 AND ppd.apply_id = pb.apply_id 
	(AND STR_TO_DATE(psa.modify_time, '%Y-%m-%d %H:%i:%s') <= 'xxxx-xx-xx xx:xx:xx');
1
2
3
4
5
6
7
8
9
10