调整mysql查询以从今天过滤掉日期

Question

Hi all i currently have this sql:

SELECT a.*
FROM (SELECT a.*
      FROM articles a
      WHERE date >= UNIX_TIMESTAMP(DATE(NOW() - INTERVAL 7 DAY)) AND a.active = 1
      ORDER BY views ASC
     ) a
ORDER BY views ASC

It lists all articles posted in the last week, what I want to do is adjust it so it ignored today, is that easy to do?

Answer 1

Certainly. You just need to add AND date < UNIX_TIMESTAMP(CURDATE())

For simplicity, you can use the BETWEEN operator:

WHERE `date` BETWEEN UNIX_TIMESTAMP(DATE(NOW() - INTERVAL 7 DAY))
                       AND UNIX_TIMESTAMP(DATE(NOW() - INTERVAL 1 DAY))

I believe this allows the engine to make better use of indexes than individual >= and <= calls, but I'm not certain on that.