Mysql常用日期时间函数

gistfile1.sql

#天数
TO_DAYS(NOW())-TO_DAYS(t.end_time) = 1

#分钟
TIMESTAMPDIFF(MINUTE,ms.add_date,NOW()) =3600

#几天前
DATE_SUB(NOW(),INTERVAL 7 DAY) 

# 分组排名
set @pa=0;
SELECT user_id,unit_class_id,rank FROM 
    (SELECT *,@rownum:=@rownum+1 AS rownum,IF(@pa=ff.unit_class_id,@rank:=@rank+1,@rank:=1) AS rank,@pa:=ff.unit_class_id 
    FROM 
       (SELECT user_id,unit_class_id, AVG( score)  as score,count(*) as num  FROM paper_study 
where  add_date>= DATE_SUB(NOW(),INTERVAL 7 DAY) and add_date<NOW() and status =2 and unit_class_id >0
GROUP BY unit_class_id,user_id ORDER BY unit_class_id ASC,score DESC,num desc) ff,(SELECT @rank:=0,@rownum:=0,@pa=NULL) tt) result