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