http://8aa512c4.wiz03.com/share/s/2aFhb41ayQ4W29lNzu3wxPge1xxUnW2Lv4YE25hm1n0OFX5x
时间范围数据检索
1、查询当天数据
SELECT * FROM items WHERE TO_DAYS(NOW()) = TO_DAYS(createtime)
2、查询昨天数据
SELECT * FROM items WHERE TO_DAYS(NOW())-TO_DAYS(createtime) = 1
3、查询指定n天的数据
函数:SUBDATE()函数从日期减去指定的时间间隔;CURDATE() 函数返回当前的日期。
SELECT * FROM items WHERE SUBDATE(CURDATE(),INTERVAL 2 DAY) <= DATE(createtime)
4、按月查询
#本月
SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, ‘%Y%m’ ) = DATE_FORMAT( CURDATE( ) , ‘%Y%m’ )
#上一月
SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , ‘%Y%m’ ) , date_format( 时间字段名, ‘%Y%m’ ) ) =1