Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to select last 30 days dates in MySQL?

Can I list somehow the dates of last 30 days in MySQL? Not from a table!

For example I think about like this:

SELECT date WHERE date BETWEEN SUBDATE(NOW(), INTERVAL 30 DAY) AND NOW();

Is that possible?

like image 528
netdjw Avatar asked Mar 21 '23 21:03

netdjw


1 Answers

I hacked this together from someone else's code, but it seems to work:

SELECT DATE_FORMAT(m1, '%d %b %Y')
FROM (
SELECT SUBDATE( NOW() , INTERVAL 30 DAY) + INTERVAL m DAY AS m1
FROM (
select @rownum:=@rownum+1 as m from
(select 1 union select 2 union select 3 union select 4) t1,
(select 1 union select 2 union select 3 union select 4) t2,
(select 1 union select 2 union select 3 union select 4) t3,
(select 1 union select 2 union select 3 union select 4) t4,
(select @rownum:=-1) t0
) d1
) d2 
WHERE m1 <= now()
ORDER BY m1

The original code by valex is here:

How to get a list of months between two dates in mysql

like image 157
The Blue Dog Avatar answered Mar 28 '23 21:03

The Blue Dog