booking dates ranges mysql php

Question

Again booking system and dates ranges. I've read this topic Comparing date ranges and many others, but can't find answer :(

Please see SQLFiddle http://sqlfiddle.com/#!2/89b2b/2

I have booking table, that have ID, ObjectID, PeriodStart and PeriodEnd fields of type DATE in MySQL
My example data looks like this:

(1, 3, '2014-08-07', '2014-08-14'),
(2, 3, '2014-08-19', '2014-08-23'),
(3, 2, '2014-08-13', '2014-08-15'),
(4, 2, '2014-08-19', '2014-08-21');

My query dates interval is (updated, was $from = '2014-08-14';):

$from = '2014-08-15';
$to = '2014-08-19'

I'd like to get ObjectIDs that are free on query dates interval.
Problem is that, object's 2 first reservation ends on 2014-08-15 and we want to make new booking started from 2014-08-15. Same thing with ending date 2014-08-19. Existing reservations start on this day. It's ok for humans because we calculate date periods as nights, but how to tell same thing to MySQL?

For these variables

$from = '2014-08-15';
$to = '2014-08-19'

I'd like to get [2, 3] as the result of query.

For second query

$from = '2014-08-14';
$to = '2014-08-19'

I'd like to get only [3] as the result of query, because ObjectID 2 is booked from 13 aug to 15 aug

`WHERE PeriodStart <= '2014-08-19' AND PeriodEnd >= '2014-08-14'` : Did I misunderstood your question or did you invert periodStart and periodEnd ? — Logar, Sep 02 '14 at 12:23

score 2 · Answer 1 · edited Nov 27 '15 at 07:14

2

The full code is:

SELECT DISTINCT ObjectID
FROM booking
WHERE ObjectID IN
(
SELECT ObjectID
FROM booking
WHERE PeriodStart <= '2014-08-19' AND PeriodEnd >= '2014-08-14'
)

#

+----------+
| OBJECTID |
+----------+
|        2 |
|        3 |
+----------+

Fiddle: http://sqlfiddle.com/#!2/89b2b/90

edited Nov 27 '15 at 07:14

Strawberry

33,750
13
40
57

answered Sep 02 '14 at 12:16

Praveen Kumar Purushothaman

164,888
24
203
252

Thanks to @GordonLinoff he found mistake. How your query does right answer if object 2 has in database booking from 13 to 15 aug? I have edited question and changed query period. – Lari13 Sep 02 '14 at 12:45
You want 13th to 15th or 15th to 19th? – Praveen Kumar Purushothaman Sep 02 '14 at 13:19
I want correct results on any entered intervals. For query `14-19 aug` must be only one result `3`, and for query `15-19 aug` must be two results `[2, 3]` – Lari13 Sep 02 '14 at 13:25
Your data is confusing now... It has two rows with the same value. Object ID has different start date and end date. `:/` – Praveen Kumar Purushothaman Sep 02 '14 at 13:39
please explain with more details. Object `3` has 2 bookings already. Object `2` has also 2 bookings. What same value? – Lari13 Sep 02 '14 at 13:46
Yup.. I was saying about the Object `3`. It has two bookings and it matches with the boundaries of the query. That's what I am confused. So we surely need a `Distinct()` function here. – Praveen Kumar Purushothaman Sep 02 '14 at 13:47
Yes, it matches with the boundaries of the query, because we operate with human's term `night`. New booking may start `14 aug` and may end `19 aug`. But your `SQL` doesn't return right results :( – Lari13 Sep 02 '14 at 19:42
Sorry for the anachronistic comment but DISTINCT is not a function – Strawberry Nov 21 '15 at 10:30

Gordon Linoff · Answer 2 · 2014-09-02T12:23:48.900

0

You just have a situation where the end date is not inclusive. I would express the query as:

select b.objectid
from booking b
group by b.objectid
having sum(b.PeriodStart < '2014-08-19' and B.PeriodEnd > '2014-08-14') = 0

This counts the number of times there is a booking that covers the nights you care about. If there are any, then the object is filtered out.

edited Sep 02 '14 at 12:23

answered Sep 02 '14 at 12:17

Gordon Linoff

1,242,037
58
646
786

Is there a reason for the downvote on what appears to be working logic? (http://sqlfiddle.com/#!2/89b2b/14) – Gordon Linoff Sep 02 '14 at 12:24
Your query is fine in my opinion, or i really don't get what op wants. He says he wants objects 2, 3 in results, but object 2 has a booking during 2014-08-14 so.. – Logar Sep 02 '14 at 12:32
@Logar thanks. My mistake. I want to query dates `14-19 aug` only ObjectID **3**, and for query `15-19 aug` both `[2, 3]`. I've updated the question. Sorry for mistake :( – Lari13 Sep 02 '14 at 12:47

score 0 · Answer 3 · answered Sep 02 '14 at 12:20

0

This also seems working? am i missing anything in question

SELECT DISTINCT(ObjectID)
FROM booking
WHERE PeriodStart <= '2014-08-19' AND PeriodEnd >= '2014-08-14'

answered Sep 02 '14 at 12:20

senK

2,782
1
27
38

Oh, sorry senk - overlooked your answer - but note that DISTINCT is not a function! – Strawberry Sep 02 '14 at 12:24

score 0 · Answer 4 · answered Sep 02 '14 at 12:24

0

SELECT DISTINCT ObjectID
  FROM booking
 WHERE PeriodStart <= '2014-08-19' 
   AND PeriodEnd >= '2014-08-14';

http://sqlfiddle.com/#!2/89b2b/15

answered Sep 02 '14 at 12:24

Strawberry

33,750
13
40
57

Thanks for answer, but with data provided your query return wrong result. We have for object `2` booked period from `13 aug` to `15 aug`. Please, see updated question. – Lari13 Sep 02 '14 at 19:50
This doesn't return 2 and 3? Now I'm confused. Maybe you need a more properly representative data set and desired result set. – Strawberry Sep 02 '14 at 22:43

booking dates ranges mysql php

4 Answers4

#

Fiddle: http://sqlfiddle.com/#!2/89b2b/90