如何在mysql / php中一起匹配日期和时间

Question

Date 1 :- 2014-09-27 10:00:00
Date 2 :- 2014-09-29 11:00:00

This range is stored inside the database and it means that user has given time range for 2 days from 27 to 29 and timing from 27 -> 10:00:00 to 29 -> 11:00:00. This means that user will be available from 10 AM to 11 AM between 27 to 29, 2014.

Now if i pass 2014-09-28 13:00:00 which is in date range and also in time range because user specified the entire day for it as can be seen in the range.

SELECT * FROM TABLE_NAME WHERE Id = $Id AND DATE('$currentDate') BETWEEN DATE(From_DateTime) AND DATE(To_DateTime) AND TIME('$timeHour') BETWEEN TIME(From_DateTime) AND TIME(To_DateTime)

From_DateTime = Date1
To_DateTime = Date2
currentDate = 2014-09-28
timeHour = 13:00:00

Now the problem is that logically the parameter passed are within the range but using the query its not because in TIME its not checking the date, 13 is not between 10 & 11 so its not working. I have tried the DATETIME as well but its not working as giving me error.

I need a way to match date & time both at the same time. Anyone having any suggestion. I am using PHP as programming language.

Answer 1

If the values are stored in DATETIME like this format you mentioned

Date 1 :- 2014-09-27 10:00:00
Date 2 :- 2014-09-29 11:00:00

Then you don't even need all that complexity. Just use normal comparison operators

SELECT * FROM yourTABLE
WHERE startDate >= '2014-09-27 13:00:00'
AND endDate <= '2014-09-29 10:00:00'

Ofcourse you can use your PHP variables instead of the test dates I have there in the query. You can format your PHP values to be in line with MySQL date time format.