MySQL:选择上个月和之前一个月 [英] MySQL: Select previous month and month before

问题描述

我正在尝试编写一个报告页面，该页面将

I am trying to write a reporting page that will

(部分A)选择当前日历月中的记录(因此，如果在6月10日访问，它将显示6月1日至6月10日的记录)

(PART A) Select records from the current calendar month (so if accessed on June 10, it will show records from June 1 to June 10)

(部分B)，然后是上一个日历月的另一部分(即5月1日至5月31日).

(PART B) Then another section for the previous calendar month (so May 1 to May 31).

在本讨论中，我一直在研究示例 MySQL查询以进行计算前一个月 但是我有点困惑.

I've played around with the example in this discussion MySQL Query to calculate the Previous Month But am a little bit confused.

将

SELECT Count(*) 
FROM `table` 
WHERE table.timestamp BETWEEN date_format(NOW() - INTERVAL 1 MONTH, '%Y-%m-01') AND last_day(NOW() - INTERVAL 1 MONTH)

满足(PART A)或(PART B)? 似乎是(部分A)，但我很困惑.

satisfy (PART A) or (PART B)? It seems like it is (PART A) but I am pretty confused.

您能帮我建立两个查询吗?这些SQL日期时间的东西真的让我很头疼.

Can you help me build both queries? This SQL datetime stuff really messes with my head.

推荐答案

如果从日期中减去当月的当前日期并加1，则表示当前月份的开始.您可以使用以下逻辑:

If you subtract the current day of the month from the date and add 1, you have the beginning of the current month. You can use this logic:

where time between date_sub(curdate(), interval day(curdate()) - 1 day) and
                   date_sub(date_add(date_sub(curdate(), interval day(curdate()) - 1 day), interval 1 month), interval -1 day)

这篇关于MySQL:选择上个月和之前一个月的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！