Mysql – Generate Dates between Date Ranges in thesql

ctedateMySQL

Is there a way to generate dates between date ranges. After looking on SO I found out there is a way to use CTE, another option is to use Union All from 0 to 9.
Is there an inbuilt function which I can use to generate dates between date range?

We are using MySQL 8.0.

Best Answer

I tried this solution :

WITH recursive Date_Ranges AS (
    select '2018-11-30' as Date
   union all
   select Date + interval 1 day
   from Date_Ranges
   where Date < '2018-12-31')
select * from Date_Ranges;

Related Solutions

Mysql – Best way to handle dates prior to 1000 A.D. in MySQL

An alternative is to store each part of the date in a numeric field. So you would have three fields:

year  SMALLINT     # Store positive values for AD and negative for BC years.
month TINYINT
day   TINYINT

This way it would still be human readable. The range of values for different numeric data types in MySQL are available at Overview of Numeric Types. The storage requirements are available at Data Type Storage Requirements.

Mysql – How to select all Mondays (or any day combo like MTW, WTTh) between two given dates in MySQL

From here, I modified the query slightly to get

select adddate('2015-02-01', numlist.id) as `my_date`, 
       weekday(adddate('2015-02-01', numlist.id)) as day_no,
       dayname(adddate('2015-02-01', numlist.id)) as `day_name` 
from
(SELECT n1.i + n10.i*10 + n100.i*100 AS id
   FROM num n1 cross join num as n10 cross join num as n100) as numlist
where adddate('2015-02-01', numlist.id) <= '2015-02-28'
and dayname(adddate('2015-02-01', numlist.id)) in( 'Monday', 'Tuesday');

which gives

+------------+--------+----------+
| my_date    | day_no | day_name |
+------------+--------+----------+
| 2015-02-02 |      0 | Monday   |
| 2015-02-03 |      1 | Tuesday  |
| 2015-02-09 |      0 | Monday   |
| 2015-02-10 |      1 | Tuesday  |
| 2015-02-16 |      0 | Monday   |
| 2015-02-17 |      1 | Tuesday  |
| 2015-02-23 |      0 | Monday   |
| 2015-02-24 |      1 | Tuesday  |
+------------+--------+----------+
8 rows in set (0.01 sec)

or from here

select * from 
(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date from
 (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date between '2015-02-01' and '2015-02-28'
and dayname(selected_date) in ('Monday', 'Tuesday');

result

+---------------+
| selected_date |
+---------------+
| 2015-02-02    |
| 2015-02-03    |
| 2015-02-09    |
| 2015-02-10    |
| 2015-02-16    |
| 2015-02-17    |
| 2015-02-23    |
| 2015-02-24    |
+---------------+
8 rows in set (0.23 sec)


CREATE TABLE mydate( blah date);

INSERT INTO mydate (blah)
select adddate('2015-02-01', numlist.id) as `my_date`
--       weekday(adddate('2015-02-01', numlist.id)) as day_no,
--       dayname(adddate('2015-02-01', numlist.id)) as `day_name` 
from
(SELECT n1.i + n10.i*10 + n100.i*100 AS id
   FROM num n1 cross join num as n10 cross join num as n100) as numlist
where adddate('2015-02-01', numlist.id) <= '2015-05-28'
and dayname(adddate('2015-02-01', numlist.id)) in( 'Monday', 'Tuesday');

(need a table num)

mysql> select * from mydate;
+------------+
| blah       |
+------------+
| 2015-02-02 |
| 2015-02-03 |
| 2015-02-09 |
| 2015-02-10 |
| 2015-02-16 |
| 2015-02-17 |
| 2015-02-23 |
| 2015-02-24 |
| 2015-03-02 |

insert into mydate (blah) 
select * from 
(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date from
 (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date between '2015-02-01' and '2015-05-28'
and dayname(selected_date) in ('Monday', 'Tuesday');

No table necessary. Same result as above.

Best Answer

Related Solutions

Mysql – Best way to handle dates prior to 1000 A.D. in MySQL

Mysql – How to select all Mondays (or any day combo like MTW, WTTh) between two given dates in MySQL

Related Question