CODEWITHSUNDEEP
mysqldate
Interardo
Interardo

Reputation: 53

Mysql Generate each date from date range list

I have a query (select * from bla.. bla.. ) that produce result of date range like this :

code | date1 | date2

a | 2016-04-19  | 2016-04-21 |

b | 2016-04-13  | 2016-04-14 |

I want to generate each day of that date range between date1 and date2 like this :

code | date_result

a | 2016-04-19

a | 2016-04-20

a | 2016-04-21

b | 2016-04-13

b | 2016-04-14

I found the example of query that produce each date between two date range like this :

SELECT ADDDATE('2016-04-10', INTERVAL @i:=@i+1 DAY) AS DAY
FROM (
SELECT a.a
FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
) a
JOIN (SELECT @i := -1) r1
WHERE 
@i < DATEDIFF('2016-04-19', '2016-04-10')

but I cannot implement it with my query :(

Upvotes: 2

Views: 1232

Answers (3)

Interardo
Interardo

Reputation: 53

finally I found the answer to generate dates from date1 to date2 :

select 
*
from (
    select t.*, t.date1 + INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as tanggal
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
    join (
        select date1,date2 from mytable
    ) t
    where t.date1 + INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY <= t.date2
) a

thanks for your appreciation guys

Upvotes: 0

Igor Vujovic
Igor Vujovic

Reputation: 419

Here is single query:

select a.* , from_days(t.tallyid+730485) from 
(
    select 'a' code , '2016-04-19' date1,  '2016-04-21' date2
    union all
    select 'b'code , '2016-04-13' date1,  '2016-04-14' date2,
) a
left join 
(
    SELECT @row := @row + 1 as tallyid FROM 
    (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t,
    (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t2, 
    (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t3, 
    (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t4, 
    (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t5, 
    (SELECT @row:=0) a
) t on t.tallyid between (TO_DAYS(a.date1)-730485) and (TO_DAYS(a.date2)-730485)

sequence from 1 to 100000 is created in subquery, it will work for dates between year 2000 and 2237.

Upvotes: 0

Igor Vujovic
Igor Vujovic

Reputation: 419

You can convert date to day numbers using from_days() and then do inner join with tally table (with sequential numbers from 1) Number 730485 is '2000-01-01' offset ( select from_days('2000-01-01') )

select a.* , from_days(t.tallyid+730485) from 
(
    select 'a' code , '2016-04-19' date1,  '2016-04-21' date2
    union all
    select 'b'code , '2016-04-13' date1,  '2016-04-14' date2
) a
inner join Tally t on t.tallyid between (TO_DAYS(a.date1)-730485) and (TO_DAYS(a.date2)-730485)

Upvotes: 1

Related Questions