sql 涉及时间

获取今天的数据

select * from table_name where TO_DAYS (field_name) = TO_DAYS(now())

获取昨天的数据

select *from table_name where TO_DAYS(now)-TODAYS(field_name)=1

获取昨天到今天的数据

select *from ding_personnel_corp c where (TO_DAYS(now())-TO_DAYS(field_name)) <=1

获取近7天的数据

select *from table_name where DATE_SUB(curdate(),INTERVEL 7 DAY) <= DATE(field_name)

获取本周数据

select *from table_name where YEARWEEK(DATE_FORMAT(field_name,'%Y-%m-%d')) = YEARWEEK(NOW());

获取上周数据

select *from table_name where YEARWEEK(DATE_FORMAT(field_name,'%Y-%m-%d')) = YEARWEEK(NOW())-1;

查询距离现在6个月的数据

ELECT * FROM user_event WHERE create_time BETWEEN DATE_SUB(NOW(),interval 6 month) and NOW();