php - 如何用MySQL查询落在当前时间日期内的行？

Question

如何查询落在当前时间日期内的行？

文章表，

id   title   date_from                 date_to
1    a       2014-08-11 00:00:00       2014-08-25 00:00:00
2    b       2014-08-01 00:00:00       2014-08-25 00:00:00
3    c       2014-08-09 00:00:00       2014-08-10 00:00:00

查询(显然不工作)，

SELECT 
    p.*

FROM article AS p

WHERE p.date_from >= CURRENT_TIMESTAMP
AND p.date_to <= CURRENT_TIMESTAMP
AND p.type = 'post'

我追求的结果，

1    a       2014-08-11 00:00:00       2014-08-25 00:00:00
2    b       2014-08-01 00:00:00       2014-08-25 00:00:00

Answer 1

你可以这样做

SELECT 
    p.*

FROM t AS p

WHERE NOW() >= p.date_from 
AND  NOW() <= p.date_to 

Demo

使用 CURRENT_TIMESTAMP

SELECT 
    p.*

FROM t AS p

WHERE CURRENT_TIMESTAMP >= p.date_from 
AND  CURRENT_TIMESTAMP  <= p.date_to 

Demo