CODEWITHSUNDEEP
mysqlsql
Ali Nouman
Ali Nouman

Reputation: 3412

Between two datestime in mysql

I am trying to create a reservation system in php and i have a table(field_data_field_dateres) that has two fields field_dateres_value(start date) and field_dateres_value2(end date). I want to find that if conflict occurs between reservation. Currently table has a record like thisenter image description here

currently i am writing query like this 

SELECT * FROM `field_data_field_dateres` WHERE field_dateres_value>='2014-02-14 20:15:00' and field_dateres_value2<='2014-02-14 20:30:00';

where 2014-02-14 20:15:00,2014-02-14 20:30:00 will come from php side. But its returning empty record. thanks for any help.

Upvotes: 0

Views: 58

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 181077

Since you want to find times overlapping (conflicting), the query you want is probably instead;

SELECT * FROM `field_data_field_dateres` 
WHERE field_dateres_value  < '2014-02-14 20:30:00' 
  AND field_dateres_value2 > '2014-02-14 20:15:00';

Note that the end time is compared to your new time slot's start time, and the start time is compared to your new time slot's end time. This will return all time windows in the database that overlap with your new range.

An SQLfiddle to test with.

Upvotes: 2

Related Questions