List of months within multiple date ranges in mysql

Question

I have a list of date ranges and I like to get a list of all months that are within these date ranges. I can query my date ranges like so:

Select id, start, end 
From date_range

And this query would give the following output:

1, 01-01-2016, 25-03-2016
2, 26-03-2016, 30-03-2016
3, 30-12-2016, 08-01-2017

Now I would like to find a MySQL query that just lists all months within these date ranges. So it should give the following output:

There are already examples here on how to get a list of month between two dates, such as:

But these examples are about a single date range, but I have multiple date ranges. It would be great if someone can find an sql query for my problem.

Your question is a bit confusing. Do you want all months within the date ranges, or simply the month/year of each start/end date in the ranges? — Sloan Thrasher, Aug 25 '17 at 03:07
@Sloan: I want all months within the date ranges. So if one date range spans over 5 months, then all 5 months should appear. — Pascal Klein, Aug 25 '17 at 05:35
This is really a display issue and, as such, best resolved in application code — Strawberry, Aug 25 '17 at 07:15

score 0 · Answer 1 · answered Aug 25 '17 at 10:04

Here is a solution:

#DROP TABLE IF EXISTS monthTest;
CREATE TABLE monthTest(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, `start` DATETIME, `end`DATETIME);

INSERT INTO monthTest(`start`, `end`) VALUES
    ('2016-01-01', '2016-03-25'),
    ('2016-03-26', '2016-03-30'),
    ('2016-12-30', '2017-08-01');

SELECT A.`start`, A.`end`, DATE_FORMAT(DATE_ADD(A.`start`, INTERVAL B.help_keyword_id MONTH), '%Y%m') FROM
    monthTest A,
    mysql.help_keyword B
WHERE PERIOD_DIFF(DATE_FORMAT(A.`end`, '%Y%m'), DATE_FORMAT(A.`start`, '%Y%m')) >= B.help_keyword_id
ORDER BY A.id;

Note that this query in the second JOIN table has a dependency that this table must contain more rows than the maximum number of months between any two dates and the join field must be an incrementing INTEGER starting from 0. This is due to the limitation that mysql doesn't (yet) contain a row generator so a workaround is necessary.

Regards,

James

List of months within multiple date ranges in mysql

1 Answers1