How to select a range of dates with each separate day

Question

I can of course do this in a server side language, but I wondered if it's possible to do within SQL.

I have a table like:

CREATE TABLE `dates` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `date_from` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `date_to` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`)
);

With some data like

+----+---------------------+---------------------+
| id |      date_from      |       date_to       |
+----+---------------------+---------------------+
|  1 | 2013-02-27 00:00:00 | 2013-03-01 00:00:00 |
|  2 | 2013-02-18 00:00:00 | 2013-02-20 00:00:00 |
+----+---------------------+---------------------+

(All the dates are at midnight)

I'd like to select these somehow to get back:

2013-02-27 00:00:00
2013-02-28 00:00:00
2013-03-01 00:00:00
2013-02-18 00:00:00
2013-02-19 00:00:00
2013-02-20 00:00:00

That is, all the dates in the ranges specified.

Any ideas?

this question has something to do with filling of date gaps http://www.richnetapps.com/using-mysql-generate-daily-sales-reports-filled-gaps/ — John Woo, Feb 24 '13 at 15:00
Take a look at the stored procedure here : http://stackoverflow.com/questions/510012/get-a-list-of-dates-between-two-dates — aleroot, Feb 24 '13 at 15:05

score 1 · Accepted Answer · edited May 23 '17 at 11:43

You can try with this single query(it should do the job) :

SELECT * FROM (
SELECT dates.date_from + INTERVAL a + b DAY dte
FROM
 (SELECT 0 a UNION SELECT 1 a UNION SELECT 2 UNION SELECT 3
    UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
    UNION SELECT 8 UNION SELECT 9 ) d,
 (SELECT 0 b UNION SELECT 10 UNION SELECT 20 
    UNION SELECT 30 UNION SELECT 40) m, dates
WHERE dates.date_from + INTERVAL a + b DAY  <=  dates.date_to
ORDER BY a + b ) e ORDER BY dte;

Take a look at the SQL Fiddle .

Reference Post .

How to select a range of dates with each separate day

1 Answers1