I am trying to write a reporting page that will
(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)
(PART B) Then another section for the previous calendar month (so May 1 to May 31).
I've played around with the example in this discussion MySQL Query to calculate the Previous Month
But am a little bit confused.
Will
SELECT Count(*)
FROM `table`
WHERE table.timestamp BETWEEN date_format(NOW() - INTERVAL 1 MONTH, '%Y-%m-01') AND last_day(NOW() - INTERVAL 1 MONTH)
satisfy (PART A) or (PART B)?
It seems like it is (PART A) but I am pretty confused.
Can you help me build both queries? This SQL datetime stuff really messes with my head.
解决方案
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)