How to get all dates between two dates?

Question

Please look at my books table:

books

+-----+----------+------------+------------+
| id  | venue_id | from_date  | to_date    |
+-----+----------+------------+------------+
|  1  |  8       | 2015-07-21 | 2015-07-28 |
|  2  |  5       | 2015-08-03 | 2015-08-25 |
+-----+----------+------------+------------+

I want to view all dates between from_date and to_date for which venue_id = 8

My output should be:

Array
(
    [0] => Array
        (
             [id] => 1
             [venue_id] => 8
             [today] => 2015-07-21
        )
    [1] => Array
        (
             [id] => 1
             [venue_id] => 8
             [today] => 2015-07-22
        )
    [2] => Array
        (
             [id] => 1
             [venue_id] => 8
             [today] => 2015-07-23
        )
    [3] => Array
        (
             [id] => 1
             [venue_id] => 8
             [today] => 2015-07-24
        )
    [4] => Array
        (
             [id] => 1
             [venue_id] => 8
             [today] => 2015-07-25
        )
    [5] => Array
        (
             [id] => 1
             [venue_id] => 8
             [today] => 2015-07-26
        )
    [6] => Array
        (
             [id] => 1
             [venue_id] => 8
             [today] => 2015-07-27
        )
)

today calculates from from_date date to to_date date. But my mind is blank about that query. How to write a MySQL query to get output like the output shown above?

Your output omits the end date — is that correct? So no data should be shown if the start date and the end date are the same? Or do you ensure that the end date is always a later date than the start date? Are you open to creating a table listing dates? It then becomes easy. Otherwise, it is not easy. — Jonathan Leffler, Jul 21 '15 at 05:56

Stand Still · Answer 1 · 2015-07-21T08:43:02.480

I think the below query should fetch the results. Though I haven't tested the query.

select b.id,b.venue_id,d.date from books b 
join
(select a.Date, "8" as venue_id2
from (
select curdate() - INTERVAL (a.a + (10 * b.a) + (1000 * 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
where a.Date between '2010-01-20' and '2010-01-24') d on d.venue_id2 = b.venue_id and b.venue_id = 8

A part of the answer is from this post http://stackoverflow.com/questions/2157282/generate-days-from-date-range. Just in case you need. — Stand Still, Jul 21 '15 at 06:36
I am getting this error `#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'join select a.Date as today from ( select curdate() - INTERVAL (a.a + (10 * b' at line 2` — Chinmay235, Jul 21 '15 at 07:04

score 0 · Answer 2 · answered Jul 21 '15 at 05:40

0

Following query should be use to get desired result. select * from books where venue_id=8 and from_date>='2015-07-21' and to_date<='2015-07-27'

answered Jul 21 '15 at 05:40

shankar kumar

648
5
9

How does that generate the record for 2015-07-23? – Jonathan Leffler Jul 21 '15 at 05:54
can you let me know what will be logic to calculate today from from_date and to_date – shankar kumar Jul 21 '15 at 06:00
I don't have a better solution on hand than a table containing one row for each date in the range that's relevant to the application on hand. It isn't a very big table, even if your data goes back to Roman times and forward to the end of the 10th millennium (less than 5 million rows; more likely, a lot less than 500,000 rows). – Jonathan Leffler Jul 21 '15 at 06:03
if you leave to_date field and concentrate only one table then we can use between clause to get list of data by range of dates like select * from books where venue_id=8 and from_date between '2015-07-21' and '2015-07-27' – shankar kumar Jul 21 '15 at 06:40
How do you generate the seven rows of output data from one row of input data? Remember, in the general case, there could be a longer gap than seven days, and different rows in the table would generate different numbers of rows in the output. – Jonathan Leffler Jul 21 '15 at 06:42
venue_id and date range will be dynamic and will change according to need. query structure will remain same to get that. – shankar kumar Jul 21 '15 at 06:52
At least one of us is missing the point of the question — or, at least, we have different and apparently irreconcilable views on what the question entails, or the mechanisms that can be used to provide the answer. I won't be responding for a while — 8 hours or more. – Jonathan Leffler Jul 21 '15 at 07:07

How to get all dates between two dates?

2 Answers2