CODEWITHSUNDEEP
mysqldatedatetimedifference
Padmanathan J
Padmanathan J

Reputation: 4620

How to display all the dates between multiple two dates in a table?

I want to display all the dates between two dates for a particular record

And this is the table :

ID Start_Date End_Date

1 2013-01-14 2013-01-18
2 2013-02-01 2013-02-04

and now i want to get all the dates between from date and to date.

Expected output

ID Date
1 2013-01-14
1 2013-01-15
1 2013-01-16
1 2013-01-17
1 2013-01-18
2 2013-02-01
2 2013-02-02
2 2013-02-03
2 2013-02-04

guide me to write query for that with out creating any extra tables.

I have already tried this following query

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 '2012-02-10' and '2012-02-15'

its working fine for single record. but i want get all the date intervals from my table

Updated

i have 6 qty of chairs for all days. So one user book 3 chars on 2013-01-14 to 2013-01-18 another person book 2 chars on 2013-01-17 to 2013-01-20. So my expected output shown below.

ID Date         Available
1 2013-01-14     3
1 2013-01-15     3
1 2013-01-16     3
1 2013-01-17     5 
1 2013-01-18     5
1 2013-01-19     2
1 2013-01-20     2 
1 2013-01-21     2

Upvotes: 0

Views: 223

Answers (2)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

How about 

  select
  t.id,
  DATE_FORMAT(a.Date,'%Y-%m-%d') as Date
  from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
    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 test t on a.Date between t.Start_Date and t.End_Date
  order by t.id,Date

DEMO

Upvotes: 2

David Hoelzer
David Hoelzer

Reputation: 16361

First, I don't know your specific use case, but this feels ripe for more optimization. However, how about this?

 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 '2012-02-10' and '2012-02-15') OR
      (selected_date between '2012-03-10' and '2012-03-15') OR
      (selected_date between '2012-04-10' and '2012-04-15')

Upvotes: 1

Related Questions