mysql日期查询

//当天日期sql查询条件

$todate=" date_format(from_unixtime(newstime),'%Y-%m-%d') = date_format(now(),'%Y-%m-%d')";

mysql 查询当天：

SELECT * FROM [!db.pre!]ecms_news WHERE DATE_FORMAT( FROM_UNIXTIME( newstime ) , '%Y-%m-%d' ) = DATE_FORMAT( now( ) , '%Y-%m-%d' ) ORDER BY `id` DESC

明天：

SELECT * 
FROM [!db.pre!]ecms_news
WHERE DATE_FORMAT( FROM_UNIXTIME( newstime -86400 ) , '%Y-%m-%d' ) = DATE_FORMAT( now( ) , '%Y-%m-%d' ) 
ORDER BY `id` DESC

后天：

SELECT * 
FROM [!db.pre!]ecms_news
WHERE DATE_FORMAT( FROM_UNIXTIME( newstime -86400 *2 ) , '%Y-%m-%d' ) = DATE_FORMAT( now( ) , '%Y-%m-%d' ) 
ORDER BY `id` DESC

昨天：

SELECT * 
FROM [!db.pre!]ecms_news
WHERE DATE_FORMAT( FROM_UNIXTIME( newstime +86400 ) , '%Y-%m-%d' ) = DATE_FORMAT( now( ) , '%Y-%m-%d' ) 
ORDER BY `id` DESC 

前天：

SELECT * 
FROM [!db.pre!]ecms_news
WHERE DATE_FORMAT( FROM_UNIXTIME( newstime +86400 *2 ) , '%Y-%m-%d' ) = DATE_FORMAT( now( ) , '%Y-%m-%d' ) 
ORDER BY `id` DESC

某个整点查询(%H为24小时制)：

select * from [!db.pre!]ecms_news where DATE_FORMAT( FROM_UNIXTIME( newstime ) , '%Y-%m-%d %H:%i:00' ) = DATE_FORMAT( now( ) , '%Y-%m-%d 08:00:00' ) and checked=1 and classid=1 order by id

当前时间30分之前已开服：

select * from [!db.pre!]ecms_news where DATE_FORMAT( FROM_UNIXTIME( newstime ) , '%Y-%m-%d' ) = DATE_FORMAT( now( ) , '%Y-%m-%d' ) AND DATE_FORMAT( FROM_UNIXTIME( newstime ) , '%Y-%m-%d %H:%i:00' ) < DATE_FORMAT( now( ) , '%Y-%m-%d %H:00:00' )  and checked=1 and classid=1 order by newstime desc

当前时间30分之后已开服：

select * from [!db.pre!]ecms_news WHERE DATE_FORMAT( FROM_UNIXTIME( newstime ) , '%Y-%m-%d' ) = DATE_FORMAT( now( ) , '%Y-%m-%d' ) AND DATE_FORMAT( FROM_UNIXTIME( newstime ) , '%Y-%m-%d %H:%i:00' ) < DATE_FORMAT( now( ) , '%Y-%m-%d %H:30:00' ) and checked=1 and classid=1 order by newstime desc

当前时间30分之前未开服：oking

SELECT * FROM [!db.pre!]ecms_news WHERE DATE_FORMAT( FROM_UNIXTIME( newstime ) , '%Y-%m-%d' ) = DATE_FORMAT( now( ) , '%Y-%m-%d' ) AND DATE_FORMAT( FROM_UNIXTIME( newstime ) , '%Y-%m-%d %H:%i:00' ) > DATE_FORMAT( now( ) , '%Y-%m-%d %H:30:00' ) AND classid IN ( 1, 3, 4, 5, 6, 7, 8, 9, 10 ) ORDER BY newstime ASC

当前时间30分之后未开服：okbding

SELECT * FROM d4com_ecms_news WHERE DATE_FORMAT( FROM_UNIXTIME( newstime ) , '%Y-%m-%d' ) = DATE_FORMAT( now( ) , '%Y-%m-%d' ) AND DATE_FORMAT( FROM_UNIXTIME( newstime ) , '%Y-%m-%d %H:%i:00' ) > DATE_FORMAT( now( ) , '%Y-%m-%d %H:59:00' ) AND classid IN ( 1, 3, 4, 5, 6, 7, 8, 9, 10 ) ORDER BY newstime ASC

当前时间半点之后

SELECT * FROM `[!db.pre!]ecms_news` WHERE DATE_FORMAT( FROM_UNIXTIME( newstime ) , '%Y-%m-%d' ) = DATE_FORMAT( now( ) , '%Y-%m-%d' ) AND DATE_FORMAT( FROM_UNIXTIME( newstime ) , '%Y-%m-%d %H:%i:00' ) > DATE_FORMAT( now( ) , '%Y-%m-%d %H:00:00' ) AND DATE_FORMAT( FROM_UNIXTIME( newstime ) , '%Y-%m-%d %H:%i:00' ) < DATE_FORMAT( now( ) , '%Y-%m-%d %H:31:00' ) AND checked =1 AND classid =1 ORDER BY newstime DESC

当前时间半点之前

SELECT * FROM `[!db.pre!]ecms_news` WHERE DATE_FORMAT( FROM_UNIXTIME( newstime ) , '%Y-%m-%d' ) = DATE_FORMAT( now( ) , '%Y-%m-%d' ) AND DATE_FORMAT( FROM_UNIXTIME( newstime ) , '%Y-%m-%d %H:%i:00' ) >= DATE_FORMAT( now( ) , '%Y-%m-%d %H:00:00' ) AND DATE_FORMAT( FROM_UNIXTIME( newstime ) , '%Y-%m-%d %H:%i:00' ) < DATE_FORMAT( now( ) , '%Y-%m-%d %H:30:00' ) AND checked =1 AND classid =1 ORDER BY newstime DESC

当前时间半点之后开服信息

SELECT * FROM [!db.pre!]ecms_news WHERE DATE_FORMAT( FROM_UNIXTIME( newstime ) , '%Y-%m-%d' ) = DATE_FORMAT( now( ) , '%Y-%m-%d' ) AND DATE_FORMAT( FROM_UNIXTIME( newstime ) , '%Y-%m-%d %H:%i:00' ) < DATE_FORMAT( now( ) , '%Y-%m-%d %H:59:00' ) AND DATE_FORMAT( FROM_UNIXTIME( newstime ) , '%Y-%m-%d %H:%i:00' ) > DATE_FORMAT( now( ) , '%Y-%m-%d %H:29:00' ) AND classid IN ( 1, 3, 4, 5, 6, 7, 8, 9, 10 ) 
ORDER BY newstime ASC 

当前时间30分之后已开服 okbded

SELECT * FROM [!db.pre!]ecms_news WHERE DATE_FORMAT( FROM_UNIXTIME( newstime ) , '%Y-%m-%d' ) = DATE_FORMAT( now( ) , '%Y-%m-%d' ) AND DATE_FORMAT( FROM_UNIXTIME( newstime ) , '%Y-%m-%d %H:%i:00' ) < DATE_FORMAT( now( ) , '%Y-%m-%d %H:30:00' ) AND classid IN ( 1, 3, 4, 5, 6, 7, 8, 9, 10 ) ORDER BY newstime DESC 

当前时间30分之前已开服 oked

SELECT * FROM [!db.pre!]ecms_news WHERE DATE_FORMAT( FROM_UNIXTIME( newstime ) , '%Y-%m-%d' ) = DATE_FORMAT( now( ) , '%Y-%m-%d' ) AND DATE_FORMAT( FROM_UNIXTIME( newstime ) , '%Y-%m-%d %H:%i:00' ) < DATE_FORMAT( now( ) , '%Y-%m-%d %H:00:00' ) AND classid IN ( 1, 3, 4, 5, 6, 7, 8, 9, 10 ) ORDER BY newstime DESC

查询最新10条记录

SELECT * FROM `phome_ecms_download` ORDER BY id DESC LIMIT 10