How to get data for last week, month, etc in mysql?

Question

PHP

For example, for the last 7 days it is clear how

SELECT * FROM t WHERE date> NOW() - INTERVAL 7 DAY;

But how to get data only for the last week and for the last month?

Answer 1

select id 
from tab 
where real_time > LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 2 MONTH))
  AND real_time < DATE_ADD(LAST_DAY(CURDATE() - INTERVAL 1 MONTH), INTERVAL 1 DAY);

Answer 2

well, calculate the start and end dates of the last week or last month, and choose where date between date1 and date2

Answer 3

Hello.
This is how you can calculate the periods
per week:

SELECT CURDATE() - INTERVAL CASE WHEN DAYOFWEEK(CURDATE()) = 1 THEN 13 ELSE DAYOFWEEK(CURDATE()) + 5 END DAY -- start of the previous week
SELECT CURDATE() - INTERVAL CASE WHEN DAYOFWEEK(CURDATE()) = 1 THEN 6 ELSE DAYOFWEEK(CURDATE()) - 1 END DAY -- end of the previous week

Per month

SELECT DATE_ADD(CURDATE(), INTERVAL - DAYOFMONTH(CURDATE()) DAY) -- end of the previous month

SELECT DATE_ADD(DATE_ADD(CURDATE(), INTERVAL - DAYOFMONTH(CURDATE()) DAY), INTERVAL - DAYOFMONTH(DATE_ADD(CURDATE(), INTERVAL - DAYOFMONTH(CURDATE()) DAY)) + 1 DAY) -- start of the previous month