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");

Barranka · Accepted Answer · 2015-01-15T18:54:57.010

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

@Apolo Happy to help. Be sure to check the documentation for [`CREATE TABLE`](http://dev.mysql.com/doc/refman/5.1/en/create-table.html) and read about temporary tables. — Barranka, Jan 15 '15 at 17:55
@Apolo If you need this table to be available to more than one connection, you may want to replace the `create temporary table` in the procedure with `create table` — Barranka, Jan 15 '15 at 18:45
It's to gererate stats, so yes, I may keep it as a permanent table — Apolo, Jan 15 '15 at 18:47
Then let me edit the answer considering this to be a permanent table — Barranka, Jan 15 '15 at 18:48
I can't find any docs on the `dedup` keyword you use to delare index on multiple column. Is it to specify that this set of data should be unique ? — Apolo, Jan 16 '15 at 10:40
@Apolo "dedup" is just a label... each index must have a name, and I like to use "dedup" as a name to a unique index with multiple fileds (just a matter of personal preference ;) ) — Barranka, Jan 16 '15 at 15:28

Barranka · Answer 2 · 2019-10-18T15:12:10.570

0

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.

edited Oct 18 '19 at 15:12

answered Jan 15 '15 at 18:09

Barranka

20,547
13
65
83

My personal favorite is the procedure / temp table solution, not this one... but if you want to, you can use this one – Barranka Jan 15 '15 at 18:13
I have a large set of data (~500 000 rows in mytable) to test if this works. I will come back to tell you. Thanks a lot – Apolo Jan 15 '15 at 18:39
@Apolo Please tell me which one is faster (I think the procedure will beat the select, but I don't know for sure) – Barranka Jan 15 '15 at 18:44
This works without errors ! I'm going to do some test to tell you which one is faster – Apolo Jan 16 '15 at 10:12
I know this is quite old, but FYI this method fails if the years span multiple years. – amaster Oct 18 '19 at 15:02
1

@amaster Thanks for pointing this out! Let me edit this to make it clear – Barranka Oct 18 '19 at 15:08

score 0 · Answer 3 · answered Oct 18 '19 at 15:42

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.

MYSQL : How to select every YEAR_MONTH between two dates?

What I want to do :

WHERE I AM :

HELPER :

3 Answers3

Linked