mysql中sql实现查询当天、昨天、本月、季度的语句

获取当前系统日期时间：select SYSDATE() AS 系统日期时间;

获取当前系统年月日：select current_date AS 年月日;

获取当前系统年份：select year(CURRENT_DATE) AS 年 ;

获取当前系统月份：select month(CURRENT_DATE) AS 月;

获取当前系统日：select day(CURRENT_DATE) AS 日;

获取当前系统时间：select time(SYSDATE()) AS 系统时间;

获取当前系统小时：select HOUR(SYSDATE()) AS 系统小时;

获取当前系统分钟：select MINUTE(SYSDATE()) AS 系统分钟;

获取当前系统秒：select SECOND(SYSDATE()) AS 系统秒;

获取当前系统毫秒：select MICROSECOND(SYSDATE()) AS 系统秒;
 

列子：

-- 查询当日数据 --
 SELECT sum(integral_approval.sq_integral) AS jf,sum(integral_approval.k_integral) as kf,user_name as userName,ti_jiao_name_img as imgs  from  integral_approval
 where status=1 AND 
TO_DAYS(integral_approval.sp_time) = TO_DAYS(NOW()) GROUP BY user_name,ti_jiao_name_img ORDER BY sum(integral_approval.sq_integral) DESC;

-- 查询当月数据 --
SELECT sum(integral_approval.sq_integral) AS jf,sum(integral_approval.k_integral) as kf,user_name as userName,ti_jiao_name_img as imgs  from  integral_approval
 where status=1 AND 
date_format(integral_approval.sp_time,'%Y-%m')=date_format(now(),'%Y-%m')
GROUP BY user_name,ti_jiao_name_img ORDER BY sum(integral_approval.sq_integral) DESC;

-- 查询当年数据 --
SELECT sum(integral_approval.sq_integral) AS jf,sum(integral_approval.k_integral) as kf,user_name as userName,ti_jiao_name_img as imgs  from  integral_approval
 where status=1 AND 
date_format(integral_approval.sp_time,'%Y') = year(now())
GROUP BY user_name,ti_jiao_name_img ORDER BY sum(integral_approval.sq_integral) DESC;

-- 查询当季数据 --
SELECT sum(integral_approval.sq_integral) AS jf,sum(integral_approval.k_integral) as kf,user_name as userName,ti_jiao_name_img as imgs  from  integral_approval
 where status=1 AND 
 QUARTER(integral_approval.sp_time)=QUARTER(now())
GROUP BY user_name,ti_jiao_name_img ORDER BY sum(integral_approval.sq_integral) DESC;