sql get all dates between two table dates

Question

another SQL challenge!

I want to write a MySQL query that gets all days between two dates in one record.

opening_times

id   | begin      | end
1    | 10:00:00   | 17:00:00
2    | 10:00:00   | 18:00:00
3    | 10:00:00   | 19:00:00

opening_periods

id   | opening_time_id  | begin       | end         
1    | 3                | 2016-03-26  | 2016-03-28
2    | 2                | 2016-03-29  | 2016-04-01  
3    | 1                | 2016-04-02  | 2016-04-03

I want to have this output:

date        | begin    | end
2016-03-26  | 10:00:00 | 19:00:00
2016-03-27  | 10:00:00 | 19:00:00
2016-03-28  | 10:00:00 | 19:00:00
2016-03-29  | 10:00:00 | 18:00:00
2016-03-30  | 10:00:00 | 18:00:00
2016-03-31  | 10:00:00 | 18:00:00
2016-04-01  | 10:00:00 | 18:00:00
2016-04-02  | 10:00:00 | 17:00:00
2016-04-03  | 10:00:00 | 17:00:00

Should I use a subquery for this? Thx for pointing me in the right direction!

Just a note: There is no "PHPMyAdmin SQL". It's MySQL. PHPMyAdmin is just one of the many tools that can let you execute the query on the MySQL server. — GolezTrol, Jan 26 '17 at 21:36
Possible duplicate of [Get a list of dates between two dates](http://stackoverflow.com/questions/510012/get-a-list-of-dates-between-two-dates) — GolezTrol, Jan 26 '17 at 21:37
@ sumit: PHP is the programming language. @ GolezTrol: thx for the correction :) — Sam, Jan 26 '17 at 22:26
Consider handling issues of data display in application level code — Strawberry, Jan 26 '17 at 23:26

score 1 · Answer 1 · edited May 23 '17 at 10:29

You can solve it in mysql with a complex query. First you need to build a subquery that will generate an integer sequence, like in this answer:

SELECT @row := @row + 1 as rown 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) t1,
(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 @row:=-1) x

This will be used to generate so many rows for each entry as many days you have in your dates interval.

SELECT DATEDIFF(`end`,`begin`) as number_of_days FROM `opening_periods`

And all put together will look like this:

SELECT DATE_ADD( o.`begin`, INTERVAL days day) as date_field, t.begin, t.end
FROM `opening_periods` o INNER JOIN (
SELECT id, rown as days
FROM `opening_periods`, 
(SELECT @row := @row + 1 as rown 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) t1,
(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 @row:=-1) x) numbers_table
WHERE rown <= DATEDIFF(`end`,`begin`)) r
ON o.id = r.id
INNER JOIN `opening_times` t ON o.`opening_time_id` = t.id
ORDER BY o.id

Here is a fiddle: http://rextester.com/AKDRI84101

Wow, that's quite an intens query moni_dragu, thanks for the effort! I'll check it out because some things are realy new to me (e.g. @row: never saw that before). The difficulty will be to add my two other tables to that query: one with show periods and one with event periods. — Sam, Jan 31 '17 at 19:17
@row is a user defined variable. You can read more in the docs: https://dev.mysql.com/doc/refman/5.7/en/user-variables.html or on SO: http://stackoverflow.com/a/1010042/2022457 — moni_dragu, Feb 01 '17 at 07:40

sql get all dates between two table dates

1 Answers1