MYSQL查詢時(shí)間數(shù)據(jù)

select?*?from?表名?where?to_days(時(shí)間字段名)?=?to_days(now());

SELECT?*FROM表名WHERE?TO_DAYS(?NOW(?)?)?–?TO_DAYS(?時(shí)間字段名)?<p>7天</p><pre class="brush:php;toolbar:false">SELECT?*FROM表名?where?DATE_SUB(CURDATE(),?INTERVAL?7?DAY)?<p>近30天</p><pre class="brush:php;toolbar:false">SELECT?*FROM表名?where?DATE_SUB(CURDATE(),?INTERVAL?30?DAY)?<p>本月</p><pre class="brush:php;toolbar:false">SELECT?*FROM表名?WHERE?DATE_format(?時(shí)間字段名,?‘%Y%m’?)?=?DATE_FORMAT(?CURDATE(?)?,?‘%Y%m’?)

SELECT?*FROM表名?WHERE?PERIOD_DIFF(?date_format(?now(?)?,?‘%Y%m’?)?,?date_format(?時(shí)間字段名,?‘%Y%m’?)?)?=1

#查詢本季度數(shù)據(jù) select?*?from?`ht_invoice_information`?where?QUARTER(create_date)=QUARTER(now()); #查詢上季度數(shù)據(jù) select?*?from?`ht_invoice_information`?where?QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval?1?QUARTER)); #查詢本年數(shù)據(jù) select?*?from?`ht_invoice_information`?where?YEAR(create_date)=YEAR(NOW()); #查詢上年數(shù)據(jù) select?*?from?`ht_invoice_information`?where?year(create_date)=year(date_sub(now(),interval?1?year));

查詢當(dāng)前這周的數(shù)據(jù) SELECT?name,submittime?FROM?enterprise?WHERE?YEARWEEK(date_format(submittime,’%Y-%m-%d’))?=?YEARWEEK(now());

查詢上周的數(shù)據(jù) SELECT?name,submittime?FROM?enterprise?WHERE?YEARWEEK(date_format(submittime,’%Y-%m-%d’))?=?YEARWEEK(now())-1;

查詢當(dāng)前月份的數(shù)據(jù) select?name,submittime?from?enterprisewhere?date_format(submittime,’%Y-%m’)=date_format(now(),’%Y-%m’)

查詢距離當(dāng)前現(xiàn)在6個(gè)月的數(shù)據(jù) select?name,submittime?from?enterprise?where?submittime?between?date_sub(now(),interval?6?month)?and?now();

查詢上個(gè)月的數(shù)據(jù) select?name,submittime?from?enterprisewhere?date_format(submittime,’%Y-%m’)=date_format(DATE_SUB(curdate(),?INTERVAL?1?MONTH),’%Y-%m’)

select*from`user`whereDATE_FORMAT(pudate,‘%Y%m‘)=DATE_FORMAT(CURDATE(),‘%Y%m‘)?; select?*?from?user?where?WEEKOFYEAR(FROM_UNIXTIME(pudate,’%y-%m-%d’))?=?WEEKOFYEAR(now())

select* fromuser whereMONTH(FROM_UNIXTIME(pudate,‘%y-%m-%d‘))=MONTH(now())

select* from[user] whereYEAR(FROM_UNIXTIME(pudate,‘%y-%m-%d‘))=YEAR(now()) andMONTH(FROM_UNIXTIME(pudate,‘%y-%m-%d‘))=MONTH(now())

select* from[user] wherepudatebetween上月最后一天 and下月第一天

wheredate(regdate)=curdate();

select*fromtestwhereyear(regdate)=year(now())andmonth(regdate)=month(now())andday(regdate)=day(now())

SELECT?date(?c_instime?)?,curdate(?)FROM?`t_score`WHERE?1LIMIT?0?,?30