Mysql时间相关的查询语句

(备注:表明和字段可以打错了,根据自己需要修改
在mybatis中记得把大于,小于这类的字符转义)

时间段:between,时间段可以是合法格式的字符串例如2019-1-1

select * FROM t_user where user_time between  '2017-1-1' and '2019-1-1';

当天

select * from t_user where to_days(user_time) = to_days(now());

本周

SELECT  * FROM t_user WHERE YEARWEEK(date_format(submittime,’%Y-%m-%d’)) = YEARWEEK(now());

上一周

SELECT * FROM t_user WHERE YEARWEEK(date_format(submittime,’%Y-%m-%d’)) = YEARWEEK(now())-1;

近七天

SELECT * FROM t_user  where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(user_time)

近30天

SELECT *FROM t_user where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date( user_time)

本月

SELECT * FROM t_user WHERE DATE_FORMAT(  user_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )

上一月

SELECT * FROM t_user WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format(user_time, '%Y%m' ) ) =1

本季

select * from t_user  where QUARTER(user_time)=QUARTER(now());

上一季

select * from t_user  where QUARTER(user_time)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));

近6个月

select *  from t_user where submittime between date_sub(now(),interval 6 month) and now();

本年

select * from t_user where YEAR(user_time)=YEAR(NOW());

上一年

select * from t_user  where year(user_time)=year(date_sub(now(),interval 1 year));

上个月

select * from t_user where date_format(submittime,’%Y-%m’)=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),’%Y-%m’)