MySQL Time Special

User_event: User Event table

Create_time: A field that stores time in a table

#获取当月数据SELECT *  fromUser_eventWHEREDate_format (Create_time,'%y-%m')=Date_format (now (),'%y-%m') #获取3月份数据SELECT *  fromUser_eventWHEREDate_format (Create_time,'%y-%m')=Date_format ('2016-03-01','%y-%m') #获取三月份数据SELECT *  fromUser_eventWHERE  Year(Create_time)=' .'  and MONTH(Create_time)='3'#获取本周数据SELECT *  fromUser_eventWHEREYearweek (Date_format (Create_time,'%y-%m-%d'))=Yearweek (now ()); #查询上周的数据SELECT *  fromUser_eventWHEREYearweek (Date_format (Create_time,'%y-%m-%d'))=Yearweek (now ())-1; #查询距离当前现在6个月的数据SELECT *  fromUser_eventWHERECreate_timebetweenDate_sub (now (), interval6 Month) andNow (); #查询上个月的数据SELECT *  fromUser_eventWHEREDate_format (Create_time,'%y-%m')=Date_format (Date_sub (Curdate (), INTERVAL1 MONTH),'%y-%m')

The above statement is organized from: http://www.jb51.net/article/32277.htm

#查询今天的信息记录:SELECT *  fromUser_eventWHERETo_days (' Create_time ')=To_days (now ()); #查询昨天的信息记录:SELECT *  fromUser_eventWHERETo_days (now ())-To_days (Create_time)<= 1; #查询近7天的信息记录:SELECT *  fromUser_eventWHEREDate_sub (Curdate (), INTERVAL7  Day)<=DATE (create_time); #查询近30天的信息记录:SELECT *  fromUser_eventWHEREDate_sub (Curdate (), INTERVAL -  Day)<=DATE (create_time); #查询上一月的信息记录:SELECT *  fromUser_eventWHEREPeriod_diff (Date_format (now),'%y%m'), Date_format (Create_time,'%y%m'))=1;

The above statement is organized from: http://www.oschina.net/code/snippet_583419_22850

MySQL Time Special