sql时间相关查询

查询当天新增的条数

select * from t_report_info 
where to_days(create_time) = to_days(now());

查询指定时间新增的条数

select * from t_report_info 
where to_days(create_time) = to_days('2023-09-04');

查询30天内新增的条数

select 
date_format(create_time,'%Y-%m-%d') as evertday,
count(*) countNum
FROM t_user_info
GROUP BY evertday 
ORDER BY evertday DESC 
limit 30

查询近30天的数据

select * from t_user_login_record where date_sub(curdate(),interval 30 day) <= date(create_time)

查询本月

select * from t_user_login_record where 
date_format(create_time,'%Y%m') = date_format(curdate() , '%Y%m')

查询上个月

select * from t_user_info where 
period_diff(date_format(now(),'%Y%m') , date_format( create_time,'%Y%m' )) =1

查询本季度数据

select * from t_user_info where quarter(create_time)=quarter(now())

查询上季度数据

select * from t_user_info 
where 
QUARTER(create_time)=quarter(date_sub(now(),interval 1 quarter));

查询本年数据

select * from t_user_info where year(create_time) = year(now())

查询上年数据

select * from t_user_info 
where 
year(create_time)=year(date_sub(now(),interval 1 year))

按月分组

SELECT count(0) as count,DATE_FORMAT( create_time,'%Y-%m') as time 
FROM t_user_info  GROUP BY  time

查询当前这周的数据

select * from t_user_login_record 
where yearweek(date_format(create_time,'%Y-%m-%d')) = yearweek(now())

查询上周数据

select * from t_user_login_record 
where 
yearweek(date_format(create_time,'%Y-%m-%d')) = yearweek(now()) - 1

MySQL获取前 n 天，格式 （yyyy-MM-dd）

SELECT
	@cdate := date_add( @cdate, INTERVAL - 1 DAY ) AS dateStr 
FROM
	( SELECT @cdate := date_add( CURDATE(), INTERVAL + 1 DAY ) 
	FROM t_user_login_record) tmp1 
WHERE
	@cdate > DATE_SUB( CURDATE(), INTERVAL 30 DAY )