Php – Comparing two time values from a MySQL database


I am trying to compare two sets of times in order to find out if they're overlapping. Here is what I have at the moment..

$sql = "SELECT * FROM schedule WHERE starttime>='$starttime' AND endtime<='$endtime' AND day='$updateday'";

Now this doesn't work as it appears you cant compare time values…so I am completely unsure how this can be done?

Best Solution

Datetime fields in MySQL are stored as (for example)

'2011-05-03 17:01:00'

so you should be able to do something like

$starttime = date('Y-m-d H:i:s', $timestamp);

where $timestamp is a timestamp of the time you are concerned about. Then continue with your query.

You can make timestamps by using mktime() or strtotime() (if starting from a string representation of a time, like from an earlier MySQL query), or just time() for the current time.