mysql查询在上个月和下个月之间进行选择

Question

尝试选择从上个月月初到下个月月底的所有日期:

USE test;

SELECT *  FROM  MyTable
WHERE col_date BETWEEN DATE_ADD(DATE_ADD(NOW(), INTERVAL -1 MONTH)) 
             AND DATE_ADD(DATE_ADD(NOW(), INTERVAL +1 MONTH))

它说我的语法不正确，但我看到 DATE_ADD 函数的片段是这样使用的。

Answer 1

您可以使用 date_sub()

date_sub(now(), INTERVAL 1 MONTH)

所以查询应该是这样的

USE test;
SELECT * FROM MyTable
WHERE 
col_date BETWEEN DATE_SUB(NOW(), INTERVAL 1 MONTH)  AND DATE_ADD(NOW(), INTERVAL 1 MONTH)

这是 mysql 中的样子

mysql> select date_sub(now(), INTERVAL 1 MONTH) as previous_month , date_add(now(),INTERVAL 1 MONTH) as next_month;
+---------------------+---------------------+
| previous_month      | next_month          |
+---------------------+---------------------+
| 2014-05-06 22:27:35 | 2014-07-06 22:27:35 |
+---------------------+---------------------+
1 row in set (0.01 sec)

mysql> select NOW() - INTERVAL 1 MONTH as previous_month ,NOW() + INTERVAL 1 MONTH as next_month ;
+---------------------+---------------------+
| previous_month      | next_month          |
+---------------------+---------------------+
| 2014-05-06 22:28:39 | 2014-07-06 22:28:39 |
+---------------------+---------------------+