php - PHP/MYSQL比较本月和上个月的日期

我正在研究报告模块。我一直在比较某些代理商的本周和上周报告。

本周报告查询如下

SELECT COUNT(created_at) AS cust_count_new, agency_id, created_at FROM customers WHERE
(customers.created_at >= DATE(NOW()) - INTERVAL 6 DAY AND customers.created_at
< DATE(NOW()) + INTERVAL 1 DAY

上周报告查询如下

SELECT COUNT(created_at) AS cust_count_old, agency_id, created_at FROM customers WHERE
(customers.created_at >= DATE(NOW()) - INTERVAL 13 DAY AND customers.created_at
< DATE(NOW()) - INTERVAL 6 DAY

我正在做的是比较这周和上周的报告。现在如何将查询更改为本月和上个月（30天）。我有点困惑，所以可以提供任何帮助。

最佳答案

您可以在MONTH中将INTERVAL用作单位。另外，您可以使用CURDATE()代替DATE(NOW())来获取当前日期。

本月报表查询将是：

SELECT COUNT(created_at) AS cust_count_new,
       agency_id,
       created_at
FROM customers
WHERE customers.created_at >= CURDATE() - INTERVAL 1 MONTH AND
customers.created_at < CURDATE() + INTERVAL 1 DAY

上个月报表查询如下

SELECT COUNT(created_at) AS cust_count_new,
       agency_id,
       created_at
FROM customers
WHERE customers.created_at >= CURDATE() - INTERVAL 2 MONTH AND
customers.created_at < (CURDATE() - INTERVAL 1 MONTH) + INTERVAL 1 DAY