mysql query comparing 2 date ranges

Question

here's my data structure:

seasons
id  from         to           name      
----------------------------------------
1   2015-11-01   2015-12-15   season1   
2   2015-12-16   2015-12-30   season2   
3   2015-12-31   2016-01-20   season3   

rooms_free
id  from         to           room_id
----------------------------------------
1   2015-11-26   2015-11-30   1   
2   2015-12-19   2015-12-28   2
3   2015-12-22   2015-12-29   3

i need an sql query which will join both tables by date range returning the following result:

id_room  room_from    room_to      season_id    season_name
-------------------------------------------------------------
1        2015-11-26   2015-11-30   1            season1   
2        2015-12-19   2015-12-28   2            season2
3        2015-12-22   2015-12-29   2            season2

could this be done using normal statements or would i need a mysql function? any ideas?

ps: the really tricky part is when there's several seasons per room ..

can you expand the sample for the several seasons per room? As currently every room has only one season. And you desire output — Juan Carlos Oropeza, Dec 03 '15 at 19:32

score 4 · Answer 1 · edited May 23 '17 at 11:44

4

This is the best explanation about overlaping data ranges

Determine Whether Two Date Ranges Overlap

SQL Fiddle Demo

SELECT rf.room_id as id_room ,
       rf.from as room_from,
       rf.to as room_to,
       s.id as season_id,
       s.name as season_name
FROM rooms_free rf
JOIN seasons s
  ON (rf.From <= s.to)  and  (rf.to >= s.from)

OUTPUT

| room_id |                       from |                         to | id |    name |
|---------|----------------------------|----------------------------|----|---------|
|       1 | November, 26 2015 00:00:00 | November, 30 2015 00:00:00 |  1 | season1 |
|       2 | December, 19 2015 00:00:00 | December, 28 2015 00:00:00 |  2 | season2 |
|       3 | December, 22 2015 00:00:00 | December, 29 2015 00:00:00 |  2 | season2 |

edited May 23 '17 at 11:44

Community

1
1

answered Dec 03 '15 at 18:47

Juan Carlos Oropeza

47,252
12
78
118

@BarışKURT Is great to make better question and to prove your answer works :) – Juan Carlos Oropeza Dec 03 '15 at 18:54
@JuanCarlosOropeza, you are joining where a room is free before and after a season. But what if a room is free during a season, but not until its end or not from its start? – Lajos Arpad Dec 03 '15 at 18:59
@JuanCarlosOropeza Yes, i will use it too anymore :) – BARIS KURT Dec 03 '15 at 19:00
@JuanCarlosOropeza, come on. I do not need to read a link to be able to determine that ON (rf.From <= s.to) and (rf.to >= s.from) will not join records where rf.from <= s.to <= s.from which is a clear case when the intersection is not an empty set. Since you did not correct your error, I have to down-vote your answer. – Lajos Arpad Dec 03 '15 at 19:20
Oh, wait. You are right. I misread your sql. Let me undo the down-vote, sorry for the inconvenience. – Lajos Arpad Dec 03 '15 at 19:42
@LajosArpad That is why I point you to the link, there `Charles Bretana` make a very good explanation on why that work. – Juan Carlos Oropeza Dec 03 '15 at 19:46

Harpreet Kaur · Answer 2 · 2015-12-03T19:02:07.153

0

SELECT rooms_free.id as id_room, rooms_free.from as room_from , rooms_free.to as room_to, seasons.id as season_id, seasons.name as season_name FROM rooms_free join seasons on rooms_free.from>=seasons.from and rooms_free.to<=seasons.to

edited Dec 03 '15 at 19:02

answered Dec 03 '15 at 18:49

Harpreet Kaur

1
2

score 0 · Answer 3 · answered Dec 03 '15 at 18:57

0

select rooms_free.id as id_room, rooms_free.from as room_from, rooms_free.to as room_to, seasons.id as season_id,  seasons.name as season_name
from rooms_free
join seasons
on ((rooms.from <= seasons.from) and (seasons.from <= rooms.to)) or ((seasons.from <= rooms.from) and (rooms.from <= seasons.to))

The statement above checks for strict overlapping. If there are several seasons per room, then you can use group by, having and group_concat.

answered Dec 03 '15 at 18:57

Lajos Arpad

64,414
37
100
175

This seems unnecessarily verbose – Strawberry Dec 03 '15 at 18:59
What do you consider to be unnecessary and why? – Lajos Arpad Dec 03 '15 at 19:16
Refer to the other answers – Strawberry Dec 03 '15 at 20:24

score -1 · Answer 4 · edited Dec 03 '15 at 18:46

-1

Select rooms_free.id, rooms_free.from, rooms_free.to, seasons.id,       seasons.name 
from rooms_free , seasons 
where (rooms_free.from >= season.from and rooms_free.to <= seasons.to)

edited Dec 03 '15 at 18:46

Jay Blanchard

34,243
16
77
119

answered Dec 03 '15 at 18:45

Gouda Elalfy

6,888
1
26
38

While this code may answer the question, it would be better to include some context, explaining how it works and when to use it. Code-only answers are not useful in the long run. – Bono Dec 03 '15 at 20:26

mysql query comparing 2 date ranges

4 Answers4