5

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
4
  • It's look like a bad design for me. It is easy enough to check whether or not the given date belongs to the any predefined range, but it is not so reasonable to generate the whole set of possible dates. IMHO, sure.
    – Kondybas
    May 11, 2015 at 12:54
  • @AbhikChakraborty my Schema sqlfiddle.com/#!9/73217 May 11, 2015 at 13:37
  • @AbhikChakraborty my expected output is given above. Available means remaining rooms May 11, 2015 at 13:43
  • Question from the sqlfiddle, how you have 1 2013-01-14 3 , in the property table there are 6 rooms and in the orders table (1, 1, '2013-01-14','2013-01-18',2) for that property id 2 are booked. What is the logic of getting 3 since it does not tell the remaining 6-2 May 11, 2015 at 14:09

2 Answers 2

4

The easiest way is to have a calendar table, defined in the following way:

CREATE TABLE calendar
(
    a_day date PRIMARY KEY
) ;

... and filled with all the relevant dates (i.e.: all days from 1990-1-1 to 2100-12-31). For the sake of simplicity, we will fill it only with year 2013:

INSERT INTO 
     calendar (a_day)
VALUES
    ('2013-01-01'),
    ('2013-01-02'),
    ('2013-01-03'),
    ('2013-01-04'),
    ('2013-01-05'),
    -- everything up to
    ('2013-12-31') ;

At this point, you can just have a JOIN with the two tables; with the join condition not being an equality, but a range condition:

SELECT
     t.id, c.a_day
FROM
     t
     JOIN calendar c ON c.a_day BETWEEN t.start_date AND t.end_date 
ORDER BY
     t.id, c.a_day ;

... and get

id | a_day     
-: | :---------
 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

You can see all the setup at dbfiddle here

0

This query uses the date function because column created_at contains both date and time.

SELECT date(`created_at`)
FROM table_name
WHERE date(`created_at`) BETWEEN '2018-03-08' AND '2018-03-22'
GROUP BY DATE(`created_at`)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.