How to select last 30 days dates in MySQL?

Question

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?

Search is your friend. Look here: http://stackoverflow.com/questions/2041575/mysql-query-records-between-today-and-last-30-days — The Blue Dog, Jan 19 '14 at 17:53
So what are you trying to do if you don't want to search a table? — The Blue Dog, Jan 19 '14 at 17:58
Hmm, do you want to select last 30 dates across all tables in the database? — Anshul Goyal, Jan 19 '14 at 18:00
Not sure that you can list days like this with MySQL. You should create a function do to that : https://stackoverflow.com/questions/510012/get-a-list-of-dates-between-two-dates — sdespont, Jan 19 '14 at 18:03

score 7 · Accepted Answer · edited May 23 '17 at 12:10

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

score 0 · Answer 2 · answered Jan 19 '14 at 18:04

0

You can do this the "explicit" way. That is, generate a series of numbers and calculate the date:

select date(date_sub(now(), interval n.n day) as thedate
from (select 1 as n union all
      select 2 union all
      . . .
      select 30
     ) n

answered Jan 19 '14 at 18:04

Gordon Linoff

1,242,037
58
646
786

How to select last 30 days dates in MySQL?

2 Answers2

Linked