generate days from date range

Question

I would like to run a query like

select ... as days where `date` is between '2010-01-20' and '2010-01-24'

And return data like:

days
----------
2010-01-20
2010-01-21
2010-01-22
2010-01-23
2010-01-24

Answer 1

You cannot do that in mysql alone. You should generate the range in your server-side language of choice.

Answer 2

Alright.. Try this: http://www.devshed.com/c/a/MySQL/Delving-Deeper-into-MySQL-50/
http://dev.mysql.com/doc/refman/5.0/en/loop-statement.html
http://www.roseindia.net/sql/mysql-example/mysql-loop.shtml

Use that to, say, generate a temp table, and then do a select * on the temp table. Or output the results one at a time.
What you say you want to do can't be done with a SELECT statement, but it might be doable with things specific to MySQL.
Then again, maybe you need cursors: http://dev.mysql.com/doc/refman/5.0/en/cursors.html

Answer 3

The old school solution for doing this without a loop/cursor is to create a NUMBERS table, which has a single Integer column with values starting at 1.

CREATE TABLE  `example`.`numbers` (
  `id` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

You need to populate the table with enough records to cover your needs:

INSERT INTO NUMBERS (id) VALUES (NULL);

Once you have the NUMBERS table, you can use:

SELECT x.start_date + INTERVAL n.id-1 DAY
  FROM NUMBERS n
  JOIN (SELECT STR_TO_DATE('2010-01-20', '%Y-%m-%d') AS start_date 
          FROM DUAL) x
 WHERE x.start_date + INTERVAL n.id-1 DAY <= '2010-01-24'

The absolute low-tech solution would be:

SELECT STR_TO_DATE('2010-01-20', '%Y-%m-%d')
 FROM DUAL
UNION ALL
SELECT STR_TO_DATE('2010-01-21', '%Y-%m-%d')
 FROM DUAL
UNION ALL
SELECT STR_TO_DATE('2010-01-22', '%Y-%m-%d')
 FROM DUAL
UNION ALL
SELECT STR_TO_DATE('2010-01-23', '%Y-%m-%d')
 FROM DUAL
UNION ALL
SELECT STR_TO_DATE('2010-01-24', '%Y-%m-%d')
 FROM DUAL

What would you use it for?

---

To generate lists of dates or numbers in order to LEFT JOIN on to. You would to this in order to see where there are gaps in the data, because you are LEFT JOINing onto a list of sequencial data - null values will make it obvious where gaps exist.

Answer 4

This solution uses no loops, procedures, or temp tables. The subquery generates dates for the last thousand days, and could be extended to go as far back or forward as you wish.

select a.Date 
from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) a
where a.Date between '2010-01-20' and '2010-01-24' 

Output:

Date
----------
2010-01-24
2010-01-23
2010-01-22
2010-01-21
2010-01-20

Notes on Performance

Testing it out here, the performance is surprisingly good: the above query takes 0.0009 sec.

If we extend the subquery to generate approx. 100,000 numbers (and thus about 274 years worth of dates), it runs in 0.0458 sec.

Incidentally, this is a very portable technique that works with most databases with minor adjustments.

Answer 5

if you will ever need more then a couple days, you need a table.

http://stackoverflow.com/questions/2149688/create-a-date-range-in-mysql

then,

select from days.day, count(mytable.field) as fields from days left join mytable on day=date where date between x and y;