E
E
Evgeny Shevtsov2015-09-11 13:30:55
PHP
Evgeny Shevtsov, 2015-09-11 13:30:55

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

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 the question

In order to leave comments, you need to log in

3 answer(s)
E
Evgeny Shevtsov, 2015-09-11
@Rattlesneyk

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);

R
Ruslan Fedoseev, 2015-09-11
@martin74ua

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

H
heartdevil, 2015-09-11
@heartdevil

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

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question