无夜游魂

查询 当日当月当周数据

2017-02-23 14:54:20

当天记录

select * from table where DATE_FORMAT(b.buy_datetime,'%Y-%m-%d')=current_date()

前一天记录

select * from table where DATE_FORMAT(b.buy_datetime,'%Y-%m-%d')=date_sub(current_date(),interval 1 day)

当月记录

select * from table where DATE_FORMAT(b.buy_datetime,'%Y-%m')=DATE_FORMAT(current_date(),'%Y-%m')

//----------------------------------
一周的
 select * from table where time between UNIX_TIMESTAMP(DATE_SUB(CURDATE(),INTERVAL 7 DAY)) and UNIX_TIMESTAMP(DATE_SUB(CURDATE(),INTERVAL 7 DAY));

//SELECT DATE_ADD(current_date(), INTERVAL -8 HOUR);
//select date_sub(current_date(),interval 8 hour);

$sql = "update tea_cart set cart_clear='1' where cart_uid='".$uid."' and DATE_FORMAT(cart_time,'%Y-%m-%d') 小时用 4 hour
一个月的是1 MONTH
一年应该是1 YEAR吧
精确到秒就是 UNIX_TIMESTAMP( DATE_SUB( CURDATE( ) , INTERVAL 7 DAY ) ) + CURTIME( )

查询：select * from table where time between UNIX_TIMESTAMP(DATE_SUB(CURDATE(),INTERVAL 7 DAY)) and UNIX_TIMESTAMP(CURDATE());

查询某个月的纪录
select * from sg_shop where shop_item_time >date_format('2011-07-01','%Y-%m-%d') and shop_item_time

select * from sg_shop where DATE_FORMAT(shop_item_time,'%Y-%m-%d') > date_format('2011-07-01','%Y-%m-%d');

select * from sg_shop where shop_item_time>'2011-07-01';

select * from sg_shop where shop_item_time between '2011-07-01' and DATE_ADD('2011-07-01',interval 1 month);