MYSQL : How to select every YEAR_MONTH between two dates?

Question

What I want to do :

I have a table like this :

TABLE mytable
- ID (INT)
- START (DATETIME)
- END (DATETIME)

Let's say I have these rows :

| ID  |         START       |          END        |
|--------------------------------------------------
|  1  | 2014-01-02 00:00:00 | 2014-12-02 00:00:00 | => month between : 12
|  2  | 2014-01-03 00:00:00 | 2015-02-03 00:00:00 | => month between : 14

Note : the "month between" include the start and end months

I for each YEAR_MONTH between START and END, I want to display a row like this :

ID  |  MONTH  |  YEAR
---------------------
1   |    1    |  2014
1   |    2    |  2014
1   |    3    |  2014
1   |    4    |  2014
1   |    5    |  2014
1   |    6    |  2014
1   |    7    |  2014
1   |    8    |  2014
1   |    9    |  2014
1   |    10   |  2014
1   |    11   |  2014
1   |    12   |  2014
2   |    1    |  2014
2   |    2    |  2014
2   |    3    |  2014
2   |    4    |  2014
2   |    5    |  2014
2   |    6    |  2014
2   |    7    |  2014
2   |    8    |  2014
2   |    9    |  2014
2   |    10   |  2014
2   |    11   |  2014
2   |    12   |  2014
2   |    1    |  2015
2   |    2    |  2015

So 12 records for ID 1 and 14 for ID 2.

I'm a bit stuck when the number of month is > 12

WHERE I AM :

I'm doing this :

SELECT mytable.id,
months.id as month,
YEAR(start) as year
FROM mytable
/* Join on a list from 1 to 12 */
LEFT JOIN (SELECT 1 as id UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12)
as months ON months.id BETWEEN MONTH(start) AND MONTH(end) 
order by mytable.id, month, year

So ID 2 only has 2 rows for month 1 and 2 :

ID  |  MONTH  |  YEAR
---------------------
1   |   1     |   2014
1   |   2     |   2014
1   |   3     |   2014
1   |   4     |   2014
1   |   5     |   2014
1   |   6     |   2014
1   |   7     |   2014
1   |   8     |   2014
1   |   9     |   2014
1   |   10    |   2014
1   |   11    |   2014
1   |   12    |   2014
2   |   1     |   2014
2   |   2     |   2014

Do you have any ideas or advices for this problem ? Is there a way to extract every YEAR_MONTH between two dates ? Thank you.

HELPER :

Here is a script to create the table and insert the 2 rows mentionned :

CREATE TABLE mytable (
    id INT PRIMARY KEY auto_increment,
    start DATETIME NOT NULL,
    end DATETIME NOT NULL
);

INSERT INTO mytable (start,end) VALUES 
("2014-01-02 00:00:00","2014-12-02 00:00:00"),
("2014-01-03 00:00:00","2015-02-03 00:00:00");

Answer 1

I know I am late to the party, but I was needing a good solution and sequencing was not working for my db version.

I started with https://stackoverflow.com/a/14813173/1707323 and made a few changes to get it working for my use like in this OP.

SELECT
  DATE_FORMAT(m1, '%c') AS month_single,
  DATE_FORMAT(m1, '%Y') AS this_year
FROM
 (
  SELECT 
   '2017-08-15' +INTERVAL m MONTH 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<='2020-03-23'
ORDER BY m1

This will get all of the months between these two dates. Please notice that the start date is in the second select clause and the end date is in the final where clause. This will include the starting month and ending month as well. It could be easily modified to exclude the starting and ending months with some extra +/- INTERVALS.

Answer 2

Important: As pointed by @amaster in his comment, this answer will fail if the period spans more than two years.

(Use the following code under your own risk ;) )

---

I've found another way to do this, but it's not a simple select statement and I think it's prone to errors, but I will put it here anyway:

select mytable.id, month, year
from mytable, 
(select month, year
from 
  (select 1 as month 
  union select 2 
  union select 3
  union select 4
  union select 5
  union select 6
  union select 7
  union select 8
  union select 9
  union select 10
  union select 11
  union select 12) as a, 
  (select year(start) as year from mytable
  union select year(end) as year from mytable) as b) as a
where cast(concat_ws('-', a.year, a.month, day(mytable.start)) as date) 
      between date(mytable.start) and date(mytable.end)
order by mytable.id, year, month;

See this other SQL fiddle.

Answer 3

If I understand you correctly, you need a table with dates (year - month) between each start and end date.

There's no simple select statement that will give you this, but you can create a procedure to do it. You need to create a temporary table, fill it with the values you need and then output the result.

Here's my proposed solution (considering a permanent table):

SQL Fiddle

MySQL 5.5.32 Schema Setup:

CREATE TABLE mytable (
    id INT PRIMARY KEY auto_increment,
    start DATETIME NOT NULL,
    end DATETIME NOT NULL
)//

INSERT INTO mytable (start,end) VALUES 
("2014-01-02 00:00:00","2014-12-02 00:00:00"),
("2014-01-03 00:00:00","2015-02-03 00:00:00")//

create procedure year_month_table()
begin
  -- Declare the variables to fill the years_months table
  declare id int;
  declare start_date, end_date, d date;
  -- Declare the "done" variable for the loop that fills the table,
  -- the cursor to read the data, and the handler to check if the
  -- loop should end.
  declare done int default false;
  declare cur_mytable cursor for
    select * from mytable;
  declare continue handler for not found
    set done = true;
  -- Create the table to hold your data
  create table if not exists years_months (
    row_id int unsigned not null auto_increment primary key,
    id int not null,
    month int,
    year int,
    unique index dedup(id, year, month),
    index idx_id(id),
    index idx_year(year),
    index idx_month(month)
  );
  -- Open the cursor to read the ids and the start and end dates for each one
  open cur_mytable;
  -- Disable the indexes to speed up insertion
  alter table years_months disable keys;
  -- Start the loop
  loop_data: loop
    -- Read the values from your table and store them in the variables
    fetch cur_mytable into id, start_date, end_date;
    -- If you've reached the end of the table, then you must exit the loop
    if done then
      leave loop_data;
    end if;
    -- Initialize the date to fill the table
    set d = start_date;
    while d <= end_date do
      -- Insert the values in your table
      insert ignore into years_months (id, month, year) values (id, month(d), year(d));
      -- Increment the d variable in 1 month
      set d = date_add(d, interval +1 month);
    end while;
  end loop;
  close cur_mytable;
  -- Enable the indexes again
  alter table years_months enable keys;
  -- Show the result
  select * from years_months;
end //

Query 1:

select * from mytable

Results:

| ID |                          START |                             END |
|----|--------------------------------|---------------------------------|
|  1 | January, 02 2014 00:00:00+0000 | December, 02 2014 00:00:00+0000 |
|  2 | January, 03 2014 00:00:00+0000 | February, 03 2015 00:00:00+0000 |

Query 2:

call year_month_table()

Results:

| ROW_ID | ID | MONTH | YEAR |
|--------|----|-------|------|
|      1 |  1 |     1 | 2014 |
|      2 |  1 |     2 | 2014 |
|      3 |  1 |     3 | 2014 |
|      4 |  1 |     4 | 2014 |
|      5 |  1 |     5 | 2014 |
|      6 |  1 |     6 | 2014 |
|      7 |  1 |     7 | 2014 |
|      8 |  1 |     8 | 2014 |
|      9 |  1 |     9 | 2014 |
|     10 |  1 |    10 | 2014 |
|     11 |  1 |    11 | 2014 |
|     12 |  1 |    12 | 2014 |
|     13 |  2 |     1 | 2014 |
|     14 |  2 |     2 | 2014 |
|     15 |  2 |     3 | 2014 |
|     16 |  2 |     4 | 2014 |
|     17 |  2 |     5 | 2014 |
|     18 |  2 |     6 | 2014 |
|     19 |  2 |     7 | 2014 |
|     20 |  2 |     8 | 2014 |
|     21 |  2 |     9 | 2014 |
|     22 |  2 |    10 | 2014 |
|     23 |  2 |    11 | 2014 |
|     24 |  2 |    12 | 2014 |
|     25 |  2 |     1 | 2015 |
|     26 |  2 |     2 | 2015 |

Notice that that last select statement in the procedure is the one that outputs the result. You can execute it every time you need.

Hope this helps