CODEWITHSUNDEEP
mysqlsqldate
BeginnerProgramer
BeginnerProgramer

Reputation: 49

DateTime query searching

I have a sample database.

 Name          CheckIn                 CheckOut
Jake      2017-08-02 00:00:00    2017-08-05 00:00:00
Rowan     2017-08-07 00:00:00    2017-08-11 00:00:00
Xander    2017-08-08 00:00:00    2017-08-10 00:00:00
Anna      2017-08-09 00:00:00    2017-08-15 00:00:00
Nat       2017-08-11 00:00:00    2017-08-14 00:00:00

For example user search the date of 2017-08-08 to 2017-08-10. User want to search the all data that who has the date of 8, 9 and 10. Ex. Rowan choose the date of checkin in 7 and checkout in 10, So rowan had the the date of 7, 8, 9 10 and 11. This is the output that i want.

Name          CheckIn                 CheckOut
Rowan     2017-08-07 00:00:00    2017-08-11 00:00:00
Anna      2017-08-09 00:00:00    2017-08-15 00:00:00
Xander    2017-08-08 00:00:00    2017-08-10 00:00:00

I really dont know the logic of reservation searching help me. Thanks guys.

Upvotes: 1

Views: 44

Answers (3)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

If you want to check for overlapping intervals, then this is the way:

SELECT Name, CheckIn, CheckOut
FROM mytable
WHERE CheckIn <= '2017-08-10' AND CheckOut >= '2017-08-08';

Demo here

To better understand this you can draw a sketch depicting the search interval [2017-08-08, 2017-08-10] against the reservation interval [CheckIn, CheckOut]:

enter image description here

If:

  • CheckOut < '2017-08-08' then the reservation interval comes before the search interval
  • CheckIn > '2017-08-10` then the reservation interval comes after the search interval

Hence none of the above conditions must hold if we want the intervals to overlap. So we end up with the following predicates:

`CheckOut` >= '2017-08-08' AND `CheckIn` <= '2017-08-10`

Upvotes: 2

zarruq
zarruq

Reputation: 2465

You need a between condition for start and end date in where clause for check_in and check_out columns as below.

SELECT *
FROM table1
WHERE check_in BETWEEN '2017-08-07' AND '2017-08-10'
  OR check_out BETWEEN '2017-08-07' AND '2017-08-10'

Result

name    check_in              check_out
-------------------------------------------------
Rowan   07.08.2017 00:00:00   11.08.2017 00:00:00
Xander  08.08.2017 00:00:00   10.08.2017 00:00:00
Anna    09.08.2017 00:00:00   15.08.2017 00:00:00

You can check the demo here

Upvotes: 0

Drunken Daddy
Drunken Daddy

Reputation: 7991

try this:

SELECT * FROM test.TableName WHERE 
(DATE(CheckIn) BETWEEN DATE('2017-08-08') AND DATE('2017-08-10') )
OR
(DATE(CheckOut) BETWEEN DATE('2017-08-08') AND DATE('2017-08-10') )
OR 
(DATE('2017-08-08') BETWEEN DATE(CheckIn) AND DATE(CheckOut))
OR
(DATE('2017-08-10') BETWEEN DATE(CheckIn) AND DATE(CheckOut));

Upvotes: 1

Related Questions