<p>1、當前日期</p>
<p><strong>當前時間select DATE_SUB(curdate(),INTERVAL 0 DAY) ;</strong></p>
<p><strong>時間戳：SELECT UNIX_TIMESTAMP(CAST(SYSDATE()AS DATE))<br /><br /><br />2、明天日期<br />select DATE_SUB(curdate(),INTERVAL -1 DAY) ;<br /><br /><br />3、昨天日期<br /></strong></p>
<p>select DATE_SUB(curdate(),INTERVAL 1 DAY) ;</p>
<p>昨天時間戳：<strong>SELECT </strong>UNIX_TIMESTAMP(CAST(SYSDATE()AS DATE) - INTERVAL 1 DAY)</p>
<p> </p>
<p>4、前一個小時時間</p>
<p>select date_sub(now(), interval 1 hour);</p>
<p> </p>
<p>5、后一個小時時間</p>
<p>select date_sub(now(), interval -1 hour);</p>
<p> </p>
<p>6、前30分鐘時間</p>
<p>select date_add(now(),interval -30 minute)</p>
<p> </p>
<p>7、后30分鐘時間</p>
<p>select date_add(now(),interval 30 minute)</p>
<p> </p>
<p> </p>
<p><strong>今天</strong></p>
<div class="cnblogs_code">
<pre>select * from 表名 where to_days(時間字段名) = to_days(now());</pre>
</div>
<p><strong>昨天</strong></p>
<div class="cnblogs_code">
<pre>SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 時間字段名) <= 1</pre>
</div>
<p><strong>近7天</strong></p>
<div class="cnblogs_code">
<pre>SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(時間字段名)</pre>
</div>
<p><strong>近30天</strong></p>
<div class="cnblogs_code">
<pre>SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(時間字段名)</pre>
</div>
<p><strong>本月</strong></p>
<div class="cnblogs_code">
<pre>SELECT * FROM 表名 WHERE DATE_FORMAT( 時間字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )</pre>
</div>
<p><strong>上一月</strong></p>
<div class="cnblogs_code">
<pre>SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( 時間字段名, '%Y%m' ) ) =1</pre>
</div>
<p><strong>查詢本季度數據</strong></p>
<div class="cnblogs_code">
<pre>select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(now());</pre>
</div>
<p><strong>查詢上季度數據</strong></p>
<div class="cnblogs_code">
<pre>select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));</pre>
</div>
<p><strong>查詢本年數據</strong></p>
<div class="cnblogs_code">
<pre>select * from `ht_invoice_information` where YEAR(create_date)=YEAR(NOW());</pre>
</div>
<p><strong>查詢上年數據</strong></p>
<div class="cnblogs_code">
<pre>select * from `ht_invoice_information` where year(create_date)=year(date_sub(now(),interval 1 year));</pre>
</div>
<p><strong>查詢當前這周的數據</strong></p>
<div class="cnblogs_code">
<pre>SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now());</pre>
</div>
<p><strong>查詢上周的數據</strong></p>
<div class="cnblogs_code">
<pre>SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now())-1;</pre>
</div>
<p><strong>查詢上個月的數據</strong></p>
<div class="cnblogs_code">
<div class="cnblogs_code_toolbar"><span class="cnblogs_code_copy"><a title="復制代碼"><img src="https://common.cnblogs.com/images/copycode.gif" alt="復制代碼" /></a></span></div>
<pre>select name,submittime from enterprise where date_format(submittime,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m')
select * from user where DATE_FORMAT(pudate,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') ;
select * from user where WEEKOFYEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = WEEKOFYEAR(now())
select * from user where MONTH(FROM_UNIXTIME(pudate,'%y-%m-%d')) = MONTH(now())
select * from user where YEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = YEAR(now()) and MONTH(FROM_UNIXTIME(pudate,'%y-%m-%d')) = MONTH(now()) <br />
select * from user where pudate between 上月最后一天 and 下月第一天 </pre>
<div class="cnblogs_code_toolbar"><span class="cnblogs_code_copy"><a title="復制代碼"><img src="https://common.cnblogs.com/images/copycode.gif" alt="復制代碼" /></a></span></div>
</div>
<p><strong>查詢當前月份的數據 </strong></p>
<div class="cnblogs_code">
<pre>select name,submittime from enterprise where date_format(submittime,'%Y-%m')=date_format(now(),'%Y-%m')</pre>
</div>
<p><strong>查詢距離當前現在6個月的數據</strong></p>
<div class="cnblogs_code">
<pre>select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now();</pre>
</div>
