
Question:
I am trying to write a reporting page that will
<strong>(PART A)</strong> Select records from the current calendar month (so if accessed on June 10, it will show records from June 1 to June 10)
<strong>(PART B)</strong> Then another section for the previous calendar month (so May 1 to May 31).
I've played around with the example in this discussion <a href="https://stackoverflow.com/questions/1138190/mysql-query-to-calculate-the-previous-month" rel="nofollow">MySQL Query to calculate the Previous Month</a> 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 <strong>(PART A)</strong> or <strong>(PART B)</strong>? It seems like it is <strong>(PART A)</strong> but I am pretty confused.
Can you help me build both queries? This SQL datetime stuff really messes with my head.
Answer1: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)