mysql 时间相关sql , 按天、月、季度、年等条件进行查询

1#今天
2select *
3from or_order_task where to_days(created_date)=to_days(now());
4
5#近七天
6select *
7from or_order_task where date_sub(curdate(),interval 7 day )<=date(created_date);
8
9#近七天
10select *
11from or_order_task where date_sub(curdate(),interval 30 day )<=date(created_date);
12
13#本月
14select *
15from or_order_task
16where date_format(created_date, '%Y%m') =  date_format(curdate(), '%Y%m');
17
18#以月为纬度，取得上个月的数据 如当前时间为2018-07-02 则取数据范围为所有数据中满足字段名中月份为08-01至08-31的数据（可用于筛选下个月生日人员） #数据搜索范围可通过最后判断的值进行调整 0为当月 1是上个月 -1为下个月 以此类推
19SELECT created_date FROM or_order_task WHERE PERIOD_DIFF(DATE_FORMAT(CURDATE(),'%m'),DATE_FORMAT(created_date,'%m'))= 1;
20
21# 以年月为维度 取得下个月数据
22SELECT *FROM or_order_task WHERE PERIOD_DIFF(DATE_FORMAT(CURDATE(),'%Y%m'),DATE_FORMAT(now(),'%Y%m'))=-1
23
24#查询本季度数据
25select * from `ticket_order_detail` where QUARTER(use_time)=QUARTER(now());
26
27#查询上季度数据
28select * from `ticket_order_detail` where QUARTER(use_time)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));
29
30#查询本年数据
31select * from `ticket_order_detail` where YEAR(use_time)=YEAR(NOW());
32
33#查询上年数据
34select * from `ticket_order_detail` where year(use_time)=year(date_sub(now(),interval 1 year));
35
36-- 查询当前这周的数据
37SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,’%Y-%m-%d’)) = YEARWEEK(now());
38
39-- 查询上周的数据
40SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,’%Y-%m-%d’)) = YEARWEEK(now())-1;
41
42-- 查询当前月份的数据
43select name,submittime from enterprise where date_format(submittime,’%Y-%m’)=date_format(now(),’%Y-%m’)
44
45-- 查询距离当前现在6个月的数据
46select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now();
47
48-- 查询上个月的数据
49select name,submittime from enterprise where date_format(submittime,’%Y-%m’)=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),’%Y-%m’)
50
51-- and下月第一天
52where date(regdate) = curdate();
53select * from test where year(regdate)=year(now()) and month(regdate)=month(now()) and day(regdate)=day(now())
54SELECT date( c_instime ) ,curdate( )
55FROM `t_score`
56WHERE 1
57LIMIT 0 , 30